I am bringing an Excel file,with two worksheets, into SAS 9.2. I want each worksheet to be a data set. I add labels and informats and formats and everything works fine doing two data steps. Out of curiosity, is there a way to set labels, informats and formats for two different data sets in the same data step? In other words, can you reference a specific data sets' attributes within a given data step?
Thanks,
Adrian.
Why not? If you are using the libname method to identify the files, and bringing them in with set statements, you can always define the two (or more) desired output files in the data statement, grab the filenames by using the indsname option in the set statements to grab the filenames (see: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000173782.htm ), and then just include an if then statement to route the input records to their desired output files.
It would be especially handy if all of the input files needed the same formats and labels, etc. to be defined the same way.
Why not? If you are using the libname method to identify the files, and bringing them in with set statements, you can always define the two (or more) desired output files in the data statement, grab the filenames by using the indsname option in the set statements to grab the filenames (see: http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000173782.htm ), and then just include an if then statement to route the input records to their desired output files.
It would be especially handy if all of the input files needed the same formats and labels, etc. to be defined the same way.
Don't use a DATA step. Use PROC DATASETS instead for such metadata tasks.
Howard and KSharp,
If the files already existed as SAS files I'd agree. But, in this case since the files need to be processed anyhow and, knowing Excel files probably will need some manipulation on top of the metadata, why do you both say NOT to use a datastep? Just curious.
Art .
Ou? It looks like I don't understand what OP means exactly.
But you can always change the label or format after importing data .
You see?
Ksharp
Yes. But you need to use proc datasets - a tool of managing datasets.
data a;set sashelp.class;run;
proc datasets library=work nolist;
modify a;
attrib name label='N' format=$10. ;
quit;
Ksharp
You can create two datasets with one DATA step, so yes you can assign labels and formats to two datasets at once.
But unless the variables in the two Excel sheets are the same you will have other work to do.
There are other methods that can used to make sure attributes of variables are defined consistently across datasets.
One method is to store the statements into a macro variable and then use that in two different places.
%let attrib=%str(
attrib id length=$10 label='Patient Id';
attrib sex length=$1 label='Gender (M/F)';
);
data one;
&attrib
set excel.sheet1 ;
run;
data two;
&attrib
set excel.sheet2;
run;
Other is to reference an existing dataset that has the variables defined.
data two ;
if 0 then set one;
set excel.sheet2;
run;
However if you are using Excel as a data source you will need to check it carefully. Make sure the columns actually contain consistent values that can be treated as a variable in a dataset because Excel is designed for spread sheet calculations and any cell could contain any type of data.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.