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

Hello SAS users,

 

I have 36 individual datasets (one dataset per month for 3 years) titled "monthyear" (eg. "12012" for Jan 2012). I want to open then up, sort them, create a new variable and close them up. Preferebly, in an automated fashion.

 

In STATA, I would be able to write a loop for it. I would start the loop BEFORE opening up the dataset, and have it run through values i=1/12, j=1/3.

 

In SAS this would look something like this:

 

do i= 1 to 12;

     do j= 1/3;

     data new_'i'201'j';

     set 'i'201'j';

     sort.....;

     newvar...;

output;

run;

end;

end;

 

 

However, in SAS the DO statement must be preceded by the DATA statement that specifies the dataset. Therefore, this won't run.

 

Is there a solution for this?

 

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions
10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Well, to start with 12012 is not a valid name for a dataset, so your datasets aren't called that.  Also, is the year always 4 digits?  It would be a good idea to have some sort of standardisation to make finding month easier, is it 1 digit or 2?  

 

Now as for looping, thats pretty simple, you have the metadata information in sashelp.vtable (and vcolumns for columns) and from that you can simply create your code:

 

data _null_;
  set sashelp.vtable (where=(libname="<yourlib>" and substr(memname,1,4)="DTA1"));
  call execute('data '||catx('.',libname,memname)||'; set '||catx('.',libname,memname)||'; do some code here; run;');
run;

What this does is open the SAS metadata, and take only rows with given libname and prefix of DTA1 (as your given names are incorrect).  Then for each of these datasets it will generate a datastep with the code.

Here is an example:

data work.dta1_012012;
  set sashelp.class;
run;
data work.dta1_022012;
  set sashelp.class;
run;
data _null_;
  set sashelp.vtable (where=(libname="WORK" and substr(memname,1,4)="DTA1"));
  call execute('data '||catx('.',libname,memname)||'; set '||catx('.',libname,memname)||'; myflag=1; run;');
run;
GKati
Pyrite | Level 9

So if I understand correctly, I have to open up each individual dataset and create a work.xxx dataset out of them. 

 

If that's the case, I might as well do the entire thing with copy/paste and run the code. 

 

But thanks for the response.

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

No, this code will generate the necessary code to perform the same datastep on every dataset name returned by the where clause.  Try running the code and you will see what I mean.  You do not have to copy/paste or anything else, just run that code.

Reeza
Super User

The other solution is to write a macro, so %do loop. 

 

http://www.ats.ucla.edu/stat/sas/seminars/sas_macros_introduction/

 

 

Vish33
Lapis Lazuli | Level 10

Are you sure on the existing data set names like 12012, 21012 ..and so on.

 

Data set name should start with either '_' or a 'character' . Clarify on the existing source data.

GKati
Pyrite | Level 9

Yes, the name is as described. I had trouble with it but with '' before/after the name helped.

 

Thanks for the responses so far.

 

Kurt_Bremser
Super User

And while we're at defining names for datasets, the sequence in the filename should be year - month instead of month - year. Much easier for handling in the future.

GKati
Pyrite | Level 9

Thanks, will remember for future. For now, the name was pre-defined.

Vish33
Lapis Lazuli | Level 10

Try this way ..

 

 

%Macro test(libname=,prefix=,newvar=);

/**listing the data from a particular library where the actual data resides and by giving proper condition**/

proc sql;

create table listData as

select memname

from dictionary.tables

where libname="&libname" and memname like "&prefix%" ;/*here prefix or suffix how you are identifying data set*/

quit;

/**Count of total no.of data sets **/

proc sql;

select count(*) into :cnt from listData;

quit;

/**create macro variables for each data set..lets say in your case it is 36**/

/**like memname1=12012 memname2=22012......memname36=122014**/

proc sql;

%do i=1 to &cnt.;

select memname into :memname&i.-memname&cnt. from listData;

%end;

quit;

/** calling one by one using do loop and create the required new varibles**/

%macro all;

%do i=1 to &cnt.;

%let memname=%sysfunc(compress(&&memname&i.)

data new_&memname.;

set &memname.;

/*sort */

&newvar=;/*apply logic here if this is common for all*/

run;

%end;

%mend;

%all;

%mend;

/*invoke the macro by giving your library name and prefix or suffix*/

%test(libname=,prefix=,newvar=);

 

DanielSantos
Barite | Level 11

Hi.

 

Here's a macro I believe will do the cycle for you.

 

%macro doit(PREFIX,NAME,SUFIX);

* enable extended data names support;
options VALIDMEMNAME=EXTEND; 

%do _P=1 %to PREFIX;
%do _S=1 %to SUFIX;

* create new var;
data "&_P&NAME&_S"n;
        set "&_P&NAME&_S"n;
        <new_var>=<some_value>
run;

* sort the data by desired var;
proc sort;
        by <desired_var>;
run;

%end;
%end;
%mend doit;

%doit(12,201,3); * do it;

As said previously, data set naming explicitly forbids the use of a number at first position.

 

For that you must enable extended name support with the VALIDMEMNAME option. Then you can name you data whatever you want if enclosed in '<data name>'n or "<data name>"n.

 

More on names in SAS here: http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p18cdcs4v5wd2dn1q0x2...


Hope it helps.

 

Daniel Santos @ www.cgd.pt

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 10 replies
  • 1177 views
  • 10 likes
  • 6 in conversation