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.
Hi.
Please give us a sample of your data (revelant columns) and an example of the desired output.
Daniel Santos @ www.cgd.pt
Wide data sets make it awkward for query.
Transpose/normalize your data structure for an easier query and maintenance life.
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
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.
I missunderstood the question
However it is even easier
wher input(cats('01',month,'2016''),anydtdtm.) ne .
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.