topic Re: Keeping variables from datasets in New SAS User

Keeping variables from datasets

JayceWaylon — Mon, 17 Feb 2020 15:01:19 GMT

Is there a way to keep variables from one dataset if I am setting multiple datasets? There is an excel file of the variables that is wanted, it is only variables no data. Then I have my raw data that has the variables but has the data. I want to have a new dataset where I can set my “wanted variables” with my “raw data variables” and just keep those variables, because the raw data will have more variables than wanted.

I know I can do a simple keep statement, I am wanting it to be dynamic, so if the excel file with the desired variable list changes the new dataset will catch it and keep the new added variable. Rather than checking all the variables each time.

Re: Keeping variables from datasets

novinosrin — Mon, 17 Feb 2020 15:16:42 GMT

HI @JayceWaylon An easy and lazy way(as i am super lazy) would be to

1. Have your excel as an empty dataset

2. Use a SET operator with CORR option and append with UNION ALL

Example:


/*Your raw data*/
data two;
 set sashelp.class;
 retain k 'junk';
run;
/*Your fictitious excel*/
data excel;
 set sashelp.class;
 stop;
run;

proc sql;
create table want as
select *
from excel
union all corr
select *
from two;
quit;

There is a caveat in the above approach, the position of the variables should match in the select clause

Re: Keeping variables from datasets

novinosrin — Mon, 17 Feb 2020 15:20:42 GMT

Or a proc append is another option provided you are willing to tolerate the WARNING message in the LOG:


/*Your raw data*/
data two; 
 retain name height;
 set sashelp.class;
 retain k 'junk';
run;
/*Your fictitious excel*/
data excel;
 set sashelp.class;
 stop;
run;

proc append base= excel data=two force;
run;

Re: Keeping variables from datasets

Reeza — Mon, 17 Feb 2020 18:45:20 GMT

1. Import your Excel file, I'm going to assume you know how to do this.

2. Create a list of the variables using a macro variable

3. Use the macro variable

It really does depend on how the 'empty' variable data set is structured, though.

proc sql noprint;
select varName into :keep_list separated by " "
from importFromExcel;
quit;

data want;
set t1 (keep = &keep_list)
      t2 (keep = ID ....other variables *optional*);

run;

@JayceWaylon wrote:

Is there a way to keep variables from one dataset if I am setting multiple datasets? There is an excel file of the variables that is wanted, it is only variables no data. Then I have my raw data that has the variables but has the data. I want to have a new dataset where I can set my “wanted variables” with my “raw data variables” and just keep those variables, because the raw data will have more variables than wanted.

I know I can do a simple keep statement, I am wanting it to be dynamic, so if the excel file with the desired variable list changes the new dataset will catch it and keep the new added variable. Rather than checking all the variables each time.