topic Re: sqlloop question in SAS Programming

sqlloop question

econdon — Thu, 27 Feb 2014 22:00:53 GMT

Hi all--

Still new to SAS, and extremely new to macros. I've been using macros to loop through proc reg and proc sql just fine, but I've run into a snag. I ran proc reg on all of my dependent variables with different sets of independent variables (I performed variable elimination on each dependent variable) to get all possible models with their AIC values. Now I have models with different variables, e.g. Y1 = X1 X3 and Y2 = X1 X2 X4, etc. I have output tables that I'm trying to manipulate to obtain akaike weights and averaged parameter estimates. In order to do this, I have to use the case expression (colored blue in the code below) to create a new set of columns for each individual variable where if parameter X is present in the model, it gets a value of 1, if not it gets a value of 0.

I included an attached table as an example of one of 20 tables I need to get through this. For this particular table I need 3 new columns, one for each parameter.

Can anyone point me in the right direction here? I tried to create some %let statements, but I have to use an equal sign, which it won't accept. I'm sure there's a more efficient way to do this than writing out the code for each of my 20 dependent variables by hand. Any help would be appreciated.

%macro sqlloop(num);

%do i=1 %to &num;

proc sql;

create table Y&i.parmestpresence as

select _model_, &&col&i.,

case

when X12=0 then 0

else 1

end as X12IG label='X12 I(j)G(i)' format=best.,

case

when X13=0 then 0

else 1

end as X13IG label='X13 I(j)G(i)' format=best.,

_aicc_, _deltaaicc_, _wi_ from Y&i.akaikeweight;

title 'Y&i. Ij(gi)';

select * from Y&i.parmestpresence;

%end;

%mend;

%sqlloop(20)

Re: sqlloop question

Reeza — Thu, 27 Feb 2014 22:05:27 GMT

If your datasets aren't too big, you may want to consider BY processing instead.

The answer to your direct question depends on how your data looks, usually a proc transpose is what you'll need to give you 1's and then you can fill in the missing with 0.

Re: sqlloop question

econdon — Thu, 27 Feb 2014 22:39:46 GMT

Hi Reeza--

My data sets are pretty small, but I'm not sure what you mean by BY processing. Is that something I should do in proc sql, or should i move to proc transpose? I've never used proc transpose, but I'll look into it now.

Lizzie

Re: sqlloop question

Reeza — Thu, 27 Feb 2014 22:51:57 GMT

This isn't a proc transpose problem. Do you have a macro variable that already lets you know how many variables you have? or a naming convention for your new variables?

It's fairly easy in a data step, but i'm guessing that you have different variables in each and want to automate that somehow. But do you really need this, I'd imagine you'd first want to merge/join your twenty tables and then do something like this?

data want;

set have;

array vars(3) x12 x13 x8;

array vars_new(3) indicator_x12 indicator_x13 indicator_x8;

do i=1 to dim(vars);

ifn(vars(i)=0, 0,1);

end;

run;

Re: sqlloop question

Reeza — Thu, 27 Feb 2014 22:52:53 GMT

Example of By group processing:

proc sort data=sashelp.class; by sex;

proc reg data=class;

by sex;

model weight=height age;

run;

Re: sqlloop question

econdon — Thu, 27 Feb 2014 23:02:16 GMT

Thanks Reeza, let me give this a try on just one table. I do have the different variable names as macro statements, so I should be able to make that loop.

Re: sqlloop question

econdon — Thu, 27 Feb 2014 23:26:58 GMT

Hi Reeza--

I'm getting an error code--I think I'm missing something at the end there. Was wondering if you had an insight?

data y7deltaaiccpres;

2776 set y7deltaaicc;

2777 array vars(6) x10 x2 x3 x4 x5 x6;

2778 array vars_new(6) indicator_x10 indicator_x2 indicator_x3 indicator_x4 indicator_x5

2778! indicator_x6;

2779

2780 do i=1 to dim(vars);

2781 ifn(vars(i)=0,=0,=1);

ERROR: Undeclared array referenced: ifn.

2781 ifn(vars(i)=0,=0,=1);

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,

a numeric constant, a datetime constant, a missing value, INPUT, PUT.

2781 ifn(vars(i)=0,=0,=1);

ERROR 22-322: Syntax error, expecting one of the following: +, =.

2781 ifn(vars(i)=0,=0,=1);

ERROR 76-322: Syntax error, statement will be ignored.

2782 end;

2783 run;

Re: sqlloop question

Reeza — Thu, 27 Feb 2014 23:30:31 GMT

The function should be

vars_new(i)=ifn(vars(i)=0, 0, 1)

No equal signs.

If you're on a version of SAS that's older then try something like:

if vars(i)=0 then vars_new(i)=0;

else vars_new(i)=1;

Re: sqlloop question

econdon — Thu, 27 Feb 2014 23:42:09 GMT

Oops, sorry. I was just messing around, the old code without equal signs didn't work either.

The new code did give me an output, but all the new cells have 1's in them. Not what I'm looking for...still messing around here. Thanks for your help, this is getting me closer!

Re: sqlloop question

econdon — Thu, 27 Feb 2014 23:47:24 GMT

Oh HA! The original table did not have 0's, it had blank cells when a parameter was missing. I flipped the if/then statement to

if vars(i)<>0 then vars_new(i)=1;

else vars_new(i)=0;

and that did the trick. I think I see the next step from here. Thanks for all your help!