BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
stygian
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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.

View solution in original post

6 REPLIES 6
art297
Opal | Level 21

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.

Howles
Quartz | Level 8

Don't use a DATA step. Use PROC DATASETS instead for such metadata tasks.

art297
Opal | Level 21

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.

Ksharp
Super User

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

Ksharp
Super User

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

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1596 views
  • 0 likes
  • 5 in conversation