topic Re: question on sql dataset and macro variables in SAS Procedures

question on sql dataset and macro variables

hdg — Fri, 21 Feb 2014 15:16:03 GMT

I have the following dataset

startdate enddate parameter parmatervalue

01/31/2000 01/31/2005 nc 10

02/01/2005 01/31/2015 nc 12

01/31/2000 01/31/2005 rval 25

I am trying to write a simple macro or function really that takes in the above dataset and a date say 03/31/2005 and picks all the relevant values for each parameter

The output would be to assign local macro variables their value

So for date= 03/31/2005

nc = 12;

rval = 25

I have more parameters than the ones assigned above..

Thanks so much for your help!

Re: question on sql dataset and macro variables

Tom — Fri, 21 Feb 2014 16:03:53 GMT

%let date='31MAR2005'd ;

data _null_;

set have ;

where startdate <= &date <= enddate;

call symputx( parameter, parmatervalue);

run;

Re: question on sql dataset and macro variables

hdg — Fri, 21 Feb 2014 16:37:26 GMT

Thanks very much but these produce global macro variables, is there way to change them to local?

Re: question on sql dataset and macro variables

Barnipaz — Fri, 21 Feb 2014 16:39:35 GMT

Put befor the previous code.

%local date;

Re: question on sql dataset and macro variables

Tom — Fri, 21 Feb 2014 16:58:31 GMT

If you run it outside of macro it has to create GLOBAL macro variables since there is no other active symbol table.

It should create LOCAL macro variables if you run it inside of a macro. Only reason it wouldn't is if the macro does not already have any local macro variables. Note that if the macro being reference already exists then it will change the value of that macro variable whatever symbol table it is in, the same as a %LET statement would. You can force it to create local by adding 'L' as the third parameter to CALL SYMPUTX().

Re: question on sql dataset and macro variables

hdg — Fri, 21 Feb 2014 18:07:04 GMT

Thank you very much Tom!

Re: question on sql dataset and macro variables

hdg — Sat, 22 Feb 2014 02:07:58 GMT

Thanks very much Tom. Sorry but I have one more follow up question on this same question If I would like to create an array of values for each parameter specified based on the number of dates For example I have another dataset with all dates.

dataset A

date

01/31/2011

02/28/2011

03/31/2011

etc

and i want to look up in the above parameter table all available parameters in this case there is only two, but there can be more

and store each date in an array date and each corresponding parameter in its own array

for example just showing the pseudocode below

The 1st entry

date[1] =01/31/2011

nc[1] = 12

rval[1]=25

Many Thanks for your help!

Re: question on sql dataset and macro variables

Tom — Sat, 22 Feb 2014 02:28:58 GMT

What do you mean by an ARRAY?

If you are creating macro variables there is no such thing as an array. A macro variable only holds characters.

Re: question on sql dataset and macro variables

hdg — Sat, 22 Feb 2014 04:42:52 GMT

i mean an array of macro variables.. I guess what I am trying to do is store for each date , all the parameter values in their respective arrays

Something like

%let i = 1;

%put &date&i; *This should be for example be the first date 01/31/2011 from my dataset of all dates*/

%put &nc&i; *This would be 12

%put &rval&i; *This should be25

Thanks very very much for your help Tom!

Re: question on sql dataset and macro variables

Tom — Sat, 22 Feb 2014 05:17:47 GMT

I am not a big fan of macro variable "arrays". Seems like a lot of clutter. If you could explain what you really want to do there are probably many ways to do it that do not depend on created so many macro variables.

Not hard to do, but the question is how do you know when to increment the index variable?

data _null_;

set have ;

where (???????);

by date ;

if first.date then i+1;

call symputx(cats(parameter,i),parameter_value,'L');

run;

Re: question on sql dataset and macro variables

hdg — Sat, 22 Feb 2014 21:26:54 GMT

Thanks a lot Tom! Maybe there is a easier way to address what I am trying to do I have a dataset the below and I also have another dataset B which has a list of dates . I would like to match each of the parameters in the below dataset to each of the dates I have in B. The idea behind it being that each date has a different set of parameters which I then use as an input to another macro. I appreciate all your help!

startdate enddate parameter parmetervalue

01/31/2000 01/31/2005 nc 10

02/01/2005 01/31/2015 nc 12

01/31/2000 01/31/2005 rval 25

Re: question on sql dataset and macro variables

Tom — Sat, 22 Feb 2014 21:49:43 GMT

Just join the list of dates with the parameters and use the new table to generate the macro calls.

I still don't see how you link the parameters into sets of values. Perhaps you need another variable in your parameter table?

data parms;

length parmset startdate enddate 8 parameter $32 parametervalue $200;

informat startdate enddate mmddyy10.;

format startdate enddate yymmdd10.;

input parmset -- parametervalue ;

cards;

1 01/31/2000 01/31/2005 nc 10

1 01/31/2000 01/31/2005 rval 25

2 02/01/2005 01/31/2015 nc 12

;;;;

data dates ;

length case date 8 ;

informat date yymmdd10.;

format date yymmdd10.;

input case date @@;

cards;

1 2004/02/21 2 2008/01/01

;;;;

proc sql ;

create table runs as

select a.*,b.*

from dates a , parms b

where a.date between b.startdate and b.enddate

order by a.case,b.parmset,b.parameter

;

quit;

filename code temp;

data _null_;

set runs;

by case parmset ;

file code ;

if first.parmset then put '%mymacro' / '(' @;

else put ',' @;

put parameter '=' parametervalue ;

if last.parmset then put ');' ;

run;

%include code / source2;

%mymacro

(nc =10

,rval =25

);

%mymacro

(nc =12

);