topic Re: how to extract all values from a variable and use it to subset in SAS Programming

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

HitmonTran — Sun, 24 Jan 2021 06:08:53 GMT

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

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

mkeintz — Sun, 24 Jan 2021 07:58:56 GMT

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.

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

ChrisNZ — Sun, 24 Jan 2021 08:34:46 GMT

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

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

singhsahab — Sun, 24 Jan 2021 14:08:04 GMT

@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;

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

ballardw — Sun, 24 Jan 2021 16:02:55 GMT

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.

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

LeonidBatkhan — Sun, 24 Jan 2021 16:54:48 GMT

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.

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

HitmonTran — Sun, 24 Jan 2021 18:02:47 GMT

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)

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

HitmonTran — Sun, 24 Jan 2021 19:09:00 GMT

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;

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

Kurt_Bremser — Sun, 24 Jan 2021 22:04:51 GMT

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

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

ballardw — Mon, 25 Jan 2021 00:07:32 GMT

@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.

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

singhsahab — Mon, 25 Jan 2021 12:33:12 GMT

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