01-10-2017 05:22 AM
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.
01-10-2017 09:30 AM
Wide data sets make it awkward for query.
Transpose/normalize your data structure for an easier query and maintenance life.
01-10-2017 10:43 AM
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
01-10-2017 10:53 AM
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.