Hi,
suppose that I have a big table containing several variables. One of the variables is NAME and another is DATE.
What I want to do is to create a program, perhaps a macro, such that I can select a list of names conditional on date limits.
So the intuition of the code is something like that:
data list;
set table;
if name = (list) and date1 =< date =< date2;
run;
Thank you!
Assuming you've got a name list in table myList and your big table is called myTable, you could do:
%macro getNames(beginDate,endDate);
proc sql;
create table want as
select * from myTable
where
name in (select name from myList) and
date between "&beginDate"d and "&endDate"d;
quit;
%mend getNames;
%getNames(01JAN2014,31DEC2014);
(untested)
PG
How are the date variables determined? Is date1/date2 constants for all names or do they vary by name And come from a different table.
This type of question is better answered with SQL than a data step.
date1 and date2 can be something like 21jan2009, and not all names might have any given date
I modified the code a bit to accomodate the logic by including the two date parameters. Could you please test this and let me know if it works for your case.
proc sql;
select distinct name into :list1 - :list&sysmaxlong from have;
quit;
%put &list1 &sqlobs;
%macro test(beginDate,endDate);
%do i=1 to &sqlobs;
data &&list&i;
set have;
if name="&&list&i" and "&beginDate"d =< date =< "&endDate"d;
run;
%end;
%mend;
%test(01JAN2014,31DEC2014);
Please try this untested code
proc sql;
select distinct name into :list1 - :list&sysmaxlong from have;
quit;
%put &list1 &sqlobs;
%macro test;
%do i=1 to &sqlobs;
data &&list&i;
set have;
if name="&&list&i" and date1 =< date =< date2;
run;
%end;
%mend;
%test;
Thanks,
Jag
Assuming you've got a name list in table myList and your big table is called myTable, you could do:
%macro getNames(beginDate,endDate);
proc sql;
create table want as
select * from myTable
where
name in (select name from myList) and
date between "&beginDate"d and "&endDate"d;
quit;
%mend getNames;
%getNames(01JAN2014,31DEC2014);
(untested)
PG
Hi PG ,
your code worked.
If possible i would like to know hoe to do the following modification:
Instead of '' where name in (...)" is it possible to do ''where name in &list'' because I want to create a code which will make a &list that might contain hundreds of names like this I don't have to input them manually.
Thank you!
The macro variable would need to contain a string like: 'name1', 'name2', ...
But if you can create and populate such a macro variable then what holds you back to create a table instead and use it in the sub-select as proposed by ? Using a table has the advantage that you're not at risk to hit the maximum length of a macro variable.
myList is a second table with the list of names you want to subset on.
Otherwise its change the where condition to name in (&LIST) if you want that method. However &List has to be less than 64K chars and it would take an extra step to generate it.
%macro getNames(beginDate,endDate, List);
proc sql;
create table want as
select * from myTable
where name in (&List)
and
date between "&beginDate"d and "&endDate"d;
quit;
%mend getNames;
%getNames(01JAN2014,31DEC2014);
Put your list of values in a dataset and use where in (select var from yourlistofvalues). If you put your list in a macro variable you will run out of space and your code will be unnecessarily complicated.
@Jagadishkatam, would your code not create one dataset per item in the list? I don't think this is the intent ( plus you would create &sqlobs. * datasteps).
I believe this will be the right code for @ilikesas purpose.
/*This sql step will create a macro variable with a list of names in quotes separated by the blank space for use in the next code which creates the dataset*/
proc sql;
select distinct quote(name) into :list separated by ' ' from have;
quit;
%put &list &sqlobs;
%macro test(beginDate,endDate);
data want;
set have;
if name in ("&list") and "&beginDate"d =< date =< "&endDate"d;
run;
%mend;
%test(01JAN2014,31DEC2014);
Thanks,
Jag
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.