how to extract all values from a variable and use it to subset

[HitmonTran]

Hi,

New to SAS.

I want to extract all the values from a variable (AETERM) and  use those values to subset it later.

data:

SUBJECT    AETERM 
 1         headache
 1         fever
 2         headache
 2         fatigue
 2         cough
 2         sneeze

current code

/*1*/
proc sort data=test out=test1;
  by subject;
   where aeterm='headache';
run;

/*2*/
proc sort data=test out=test2;
  by subject;
   where aeterm='fever';
run;

/*3*/
proc sort data=test out=test3;
  by subject;
   where aeterm='fatigue';
run;

/*4*/
proc sort data=test out=test4;
  by subject;
   where aeterm='cough';
run;

/*5*/
proc sort data=test out=test5;
  by subject;
   where aeterm='sneeze';
run;
 

 

Obviously the current code would not work if there are hundreds of AETERM. My goal is to macrotize or do something similar if possible.

Thank you   

[mkeintz]

If I were doing this with dataset SASHELP.CLASS, generating one dataset for each sex, I might code:

proc sort data=sashelp.class out=have;
  by name;
run;

data test_f (where=(sex="F"))
     test_m (where=(sex="M")) ;
  set sashelp.class;
run;

Notice I do the proc sort PRIOR to dividing the datasets - no need to sort each result.

Now the above is a case where I know in advance the values of the sex variables.  But if I didn't know them in advance, then:

proc sql noprint;
  select distinct cats("test_",sex,"(where=(sex='",sex,"'))")
  into :dslist separated by ' ' 
  from have;
quit;
%put &=dslist;

data &dslist ;
  set sashelp.class;
run;

The strategy is to use the "INTO :" clause in proc sql to create a macrovars.  The (visually) tricky part is the cats function.  Segment it to look like this:

cats("test_"
    ,sex
    ,"(where=(sex='"
    ,sex
    ,"'))"
    )

The "%put &=dslist;" statement shows what macrovar was generated.  

[ChrisNZ]

Why would you want to create hundreds of tables when you have everything in one place?

[LeonidBatkhan]

Indeed, this might be a classic case of improper design. What is the final goal there? When that is formulated then a simple solution can be found, most likely without splitting your data set into many.

[HitmonTran]

The Goal is to collect all AETERM, calulate the 95% CI, then merge each AETERM's 95% CI back to the the AETERM along with other variables that were calculated earlier (eg. # of Events, # of subjects experience the event)

[Kurt_Bremser]

Please show the code you use for calculating the CI for a single AETERM.

[ballardw]

---

@HitmonTran wrote:
The Goal is to collect all AETERM, calulate the 95% CI, then merge each AETERM's 95% CI back to the the AETERM along with other variables that were calculated earlier (eg. # of Events, # of subjects experience the event)

---

Since your Aeterm variable values shown are all character I have to assume that you meant the confidence interval of another variable that is actually numeric.

Here is an example getting multiple statistics, including upper and lower confidence limits of multiple numeric variables for each level of a character (or numeric) variable.

proc summary data=sashelp.class nway;
   class sex;
   var height weight;
   output out=work.summary 
     min= mean= max= lclm= uclm= /autoname;
run;

For each level of Sex there will be min, mean, max, lower confidence limit for the mean, upper confidence limit for the mea of each variable on the VAR statement.

[singhsahab]

@HitmonTran This can be achieve by macro. you can try below code.

data have;
	length subject 8. aeterm $200.;
	input SUBJECT AETERM $;
	cards;
 1 headache
 1 fever
 2 headache
 2 fatigue
 2 cough
 2 sneeze
 ;
run;

proc sql noprint;
	select distinct(aeterm) into :aeterm1- from have;
	%let c=&SQLOBS;
quit;

%macro aeterm_table;
	%do i=1 %to &c.;

		proc sort data=have out=test&i;
			by subject;
			where aeterm eq "&&aeterm&i";
	%end;
%mend;

%aeterm_table;

[HitmonTran]

hey Singh is there a typo in proq sql step ("aeterm1-")?

proc sql noprint;
select distinct(aeterm) into :aeterm1- from have;
%let c=&SQLOBS;
quit;

[singhsahab]

It's not a typo error. it's way of creating multiple macro variable without passing upper limit value.

[ballardw]

Anything you can do with gaggle of data sets can be done with the single data set and your where condition. So there is really seldom a legitimate reason to create a bunch of data sets.

Plus the way your example works how do you keep track of the data set by value? If I see data set Test1 there is nothing telling me that the values with be "headache". And spelling can raise it's ugly head. "Headache" is not the same as "headache" is not the same as "HEADACHE".

If I want to perform analysis on those values such as a regression:

Proc reg data=test;
   where upcase(aeterm)= 'HEADACHE';
<other regression statements>

Another consideration is that what if you want to use two of these values in a single procedure? Now you have to take a separate step to recombine the data. But you can select multiple values:

Proc sgplot data=test;
   where upcase(aeterm) in ('HEADACHE' 'FEVER');
   scatter x=height y=weight/ group=aeterm;
run;

for example to create grouped plot based on values of aeterm.