Way to simplify this code?

[cdubs]

proc sql; 

create table data_2011 as
select * 
from 'E:\blah blah blah\2011.sas7bdat'
where dx in ('5015' '10383') 
quit; 

proc sql; 

create table data_2012 as
select * 
from 'E:\blah blah blah\2012.sas7bdat'
where dx in ('5015' '10383') 
quit; 

proc sql; 

create table data_2013 as
select * 
from 'E:\blah blah blah\2013.sas7bdat'
where dx in ('5015' '10383') 
quit; 

And so forth. And then I stack them all in a data step... 

Could I do something like 

%let claims = 2011 2012 2013;
%let dx = 5015 10383;

data elig_1;

set %do i=1 %to %sysfunc(countw(&claims.));
%let filename = %scan(%claims., &i.);
&filename.

where dx in %dx.

%end;;

Does the above code make sense and accomplish the same thing? 

[art297]

It would only work if you wrapped it in a macro and corrected the inconsistencies. However, the following would do the same thing:

data data_2011;
 input dx;
 cards;
5015
10383
2500
2600
50151
501
;
data data_2012;
 input dx;
 cards;
5015
10383
2500
2600
50151
501
;
data data_2013;
 input dx;
 cards;
5015
10383
2500
2600
50151
501
;

%let claims = data_2011 data_2012 data_2013;
%let dx = 5015,10383;

data elig_1;
  set &claims.;
  where dx in (&dx.);
run;

Art, CEO, AnalystFinder.com

[Tom]

Why do you have .sas7bdat files with invalid member names?  SAS Names must start with either a letter or an underscore.

You might be able to use membernames of '2012'n and '2013'n if you set the system option validmemname to EXTEND.

options validmemname=extend;
libname in 'E:\blah blah blah\';
data elig_1;
  set in.'2012'n in.'2013'n in.'2014'n ;
  where dx in ('5015' '10383');
run;

If your dataset names are valid membernames and they have numeric suffixes then you might be able to just a range of membernames instead.

set in.data2012-in.data2014 ;

If you want help in converting space delimited list of words into a list of quoted words then you can try using the TRANWRD() function.

%let dxlist=5015 10383;
%let dxlistq="%sysfunc(tranwrd(&dxlist,%str( ),%str(" ")))";

You could do a similar thing for you list of dataset names.

%let dslist=2012 2013 2014 ;
%let dslistn=in."%sysfunc(tranwrd(&dxlist,%str( ),%str("n in")))"n;

So your data step is then.

data elig_1;
  set &dslistn ;
  where dx in (&dxlistq);
run;

[cdubs]

Thanks for your response!

My SAS knowledge is too rudimentary to understand :(. I'm not sure what an invalid member name is?

I guess the original SAS dataset files are ccaes103, ccaes113, ccaes123... which correspond to 2010, 2011, 2012,...

I ultimately just want to stack them all vertically, but only taking the claims that have dx with those two codes...

[Tom · Accepted solution]

---

@cdubs wrote:

Thanks for your response!

My SAS knowledge is too rudimentary to understand :(. I'm not sure what an invalid member name is?

I guess the original SAS dataset files are ccaes103, ccaes113, ccaes123... which correspond to 2010, 2011, 2012,...

I ultimately just want to stack them all vertically, but only taking the claims that have dx with those two codes...

---

Create a SAS libref that points to the physical directory where the SAS datasets are stored. That will make it easier to refer to them and also make your code more independent of where the files get moved to a larger disk.

libname truven 'e:\directoryname';

Write a data step to read the data you want. Start by telling SAS what dataset you want to create.

data diabetes;

Then tell it what data to read.  if you want to read multiple datasets then use a space delimited list.

  set truven.ccaes13 truven.ccaes14 ;

Add any statements to limit the observations.

  where (dx1 like '250%')
     or (dx2 like '250%')
     or (dx3 like '250%')
     or (dx4 like '250%')
     or (dx5 like '250%')
  ;

You can add other data step statements to calculate new variables, etc.

End the data step.

run;

[Kurt_Bremser]

Valid SAS names contain only letters, digits, underlines, and must start with a letter or underline.

Names of physical SAS files that shall be recognized within a defined library must be lowercase. Very important with SAS UE, which runs in a UNIX VM; UNIX is a case-sensitive system.