topic Re: Do loop before data statement in SAS Programming

Do loop before data statement

GKati — Fri, 13 Jan 2017 11:39:02 GMT

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;

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,

Re: Do loop before data statement

RW9 — Fri, 13 Jan 2017 11:46:53 GMT

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;

Re: Do loop before data statement

Vish33 — Fri, 13 Jan 2017 11:58:15 GMT

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.

Re: Do loop before data statement

GKati — Fri, 13 Jan 2017 12:01:03 GMT

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

Thanks for the responses so far.

Re: Do loop before data statement

Kurt_Bremser — Fri, 13 Jan 2017 12:01:57 GMT

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.

Re: Do loop before data statement

GKati — Fri, 13 Jan 2017 12:05:15 GMT

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

Re: Do loop before data statement

GKati — Fri, 13 Jan 2017 12:07:28 GMT

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.

Re: Do loop before data statement

RW9 — Fri, 13 Jan 2017 12:18:47 GMT

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.

Re: Do loop before data statement

Reeza — Fri, 13 Jan 2017 12:23:40 GMT

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

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

Re: Do loop before data statement

Vish33 — Fri, 13 Jan 2017 13:08:02 GMT

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=);

Re: Do loop before data statement

DanielSantos — Fri, 13 Jan 2017 14:08:25 GMT

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#p18cdcs4v5wd2dn1q0x296d3qek6.htm

Hope it helps.

Daniel Santos @ www.cgd.pt