Importing from Excel: SAS Newbie

#1
I have an Excel table. In SAS 9.2, I can use File > Import Data to bring that table into the SAS Explorer pane: contents of Work now includes that file. If I double-click on it, I get a VIEWTABLE pane showing the data.

I am under the impression that it exists as a temporary file that I could save to some permanent location. But as far as using it in SAS, I'm at a loss.

My PC mindset tells me that I need appropriate syntax for making references to that file -- that, perhaps, a simple command would run a batch of univariate procedures on it, or whatever. But I don't know if that belief is accurate, and I don't know what the syntax is. I have been searching for hours and I am stuck.
 

hlsmith

Less is more. Stay pure. Stay poor.
#2
When you imported the data you gave a name for the temporary file. It should be the exact same name on the temporary dataset you opened. So use that name:

Code:
[B][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080]Proc [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/B][B][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080]Freq [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/B][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]data[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]=tempname_I_referenced;[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]table[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]
[SIZE=2][FONT=Courier New][SIZE=2][FONT=Courier New]variable_names_from_table_you_can_open[/FONT][/SIZE]
[SIZE=2][FONT=Courier New];[/FONT][/SIZE]
[/FONT][/SIZE][B][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080]run[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/B][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]; [/SIZE][/FONT][/SIZE][/FONT]
 
[B][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080]proc [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/B][B][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080]univariate [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/B][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]data[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]=temp_name_again [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]normal[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2];[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]var[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]variable_names_from_that_dataset_again;[/SIZE][/FONT]
[/SIZE][/FONT][B][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080][FONT=Courier New][SIZE=2][COLOR=#000080]run[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/B][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2];[/SIZE][/FONT]
[/SIZE][/FONT]

You cannot run data steps when the temp file is open within SAS, so make sure it is closed - that can cause problems, but otherwise you typically just have data=the_files_name for the temp file. Let us know if you still are having problems.
 
#3
This is a case where enterprise guide is really useful, because it is so simple. Once you import the file you can save it to a permament location (once you created that with a libname) simply by using PROC SQL

PROC SQL;
CREATE TABLE SASUSER.Qdata1 AS
SELECT t1.'Work Climate - 1. DVR Headq_0001'n AS x1,
t1.'Work Climate - 10. My super_0001'n AS x10,
t1.'Work Climate - 11. Managers, sup'n AS x11,
t1.'Work Climate - 12. I am abl_0001'n AS x12,
t1.'Work Climate - 13. I know w_0001'n AS x13
FROM WORK.USETHIS t1;
QUIT;

Do a libname for SASUSER first (once you do this you will never have to do the libname again, its a one time event).

Although this is done in SQL you can then use it with any SAS procedure.
 
#4
OK. I ran this:

proc freq data = Q1;
table
var IQ Arithmetic;
run;

proc corr;
var IQ Arithmetic;
run;

and it gave me some success, some failure: some useful numbers in the Output window but also this:

71
72 proc freq data = Q1;
ERROR: File WORK.Q1.DATA does not exist.
73 table
74 var IQ Arithmetic;
ERROR: No data set open to look up variables.
ERROR: No data set open to look up variables.
ERROR: No data set open to look up variables.
75 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

76


77 proc corr;
78 var IQ Arithmetic;
79 run;

But Q1 was the file I imported, and it's where the data were. So that was puzzling.
 

hlsmith

Less is more. Stay pure. Stay poor.
#5
Perhaps:

Is Q1 blue when you type it? Q1 can also mean quantile 1, and SAS may not know it is your dataset name. Even if this is not your problem, I would rename the dataset.

Also, you won't need the 'var' in the proc freq, just 'table'. It is also a good idea to also have a 'data=' in the proc corr as well. SAS uses the previously referenced set, but when you start writing alot of code or moving it around, you may run into issues in the future if you don't have the "data=".

Are you ready, do the following to find your dataset name:

Click View on the top banner,

Then "Exporer",

Then "Work",

Then right-click on the file,

Look at 'properties',

Location: will tell you the file location and name. The name is what is after 'work.'