topic Re: create subset in proc sql with selected variables in SAS Programming

create subset in proc sql with selected variables

JithinJoe — Tue, 10 Jan 2017 10:22:33 GMT

Hi whomsoever it may concern;

I have a Large mobile usauge dataset with 400+ coloums and 10000 observations , from which I have to create a subset with varibles valuse corresponding to the months "June, July and august" using proc sql. How do I can do it? As I know it can be done using macros, But I'm having hard time to do it with proc sql. I do welcome any suggestions or Idea, thank you.

Re: create subset in proc sql with selected variables

DanielSantos — Tue, 10 Jan 2017 10:54:12 GMT

Hi.

Please give us a sample of your data (revelant columns) and an example of the desired output.

Daniel Santos @ www.cgd.pt

Re: create subset in proc sql with selected variables

LinusH — Tue, 10 Jan 2017 14:30:32 GMT

Wide data sets make it awkward for query.

Transpose/normalize your data structure for an easier query and maintenance life.

Re: create subset in proc sql with selected variables

rogerjdeangelis — Tue, 10 Jan 2017 15:43:11 GMT

Not sure I completely understand your data but this will select just month variables.

* sample data;
data have;
retain x1-x5 0;
august ='haveaugust   ';
january='havejanuary  ';
run;quit;

* get month variables;
proc sql;
 select name into :nam separated by ','
 from sashelp.vcolumn
 where   libname='WORK'
     and memname='HAVE'
     and input(cats('01',name,'2016'),anydtdtm.) ne .;
 select &nam from have;
;quit;

AUGUST         JANUARY
----------------------------
haveaugust     havejanuary

Re: create subset in proc sql with selected variables

ballardw — Tue, 10 Jan 2017 15:53:18 GMT

Are the values you are looking for in the values of a single variable or multiple variables (columns if you don't use variable )?

If a single variable then you maybe looking at something like if the months are text:

proc sql;
   create table subset as
   select *
   from have
   where upcase(Monthvariable) in ('JUNE' 'JULY' 'AUGUST');
quit;

If the variable concerned is a SAS date variable (has a format like DATE9. or MMDDYY10. or similar) then

proc sql;
   create table subset as
   select *
   from have
   where month(Datevariable) in (6 7 8);
quit;

BUT if you have multiple variables involved we need to know more about the data.

Or did someone give a dataset with column headers like June2015, July2015? If this is the case then the "normalize" comments apply.

Re: create subset in proc sql with selected variables

rogerjdeangelis — Tue, 10 Jan 2017 16:02:58 GMT

I missunderstood the question

However it is even easier

wher input(cats('01',month,'2016''),anydtdtm.) ne .