create subset in proc sql with selected variables

Reply
Occasional Contributor
Posts: 8

create subset in proc sql with selected variables

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. 

Super Contributor
Posts: 474

Re: create subset in proc sql with selected variables

Hi.

 

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

 

Daniel Santos @ www.cgd.pt

Super User
Posts: 5,386

Re: create subset in proc sql with selected variables

Wide data sets make it awkward for query.

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

Data never sleeps
Valued Guide
Posts: 505

Re: create subset in proc sql with selected variables

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

Super User
Posts: 11,134

Re: create subset in proc sql with selected variables

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.

Valued Guide
Posts: 505

Re: create subset in proc sql with selected variables

I missunderstood the question

 

However it is even easier

 

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

Ask a Question
Discussion stats
  • 5 replies
  • 490 views
  • 0 likes
  • 5 in conversation