DATA Step, Macro, Functions and more

sqlloop question

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 13
Accepted Solution

sqlloop question

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 #

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)

Attachment

Accepted Solutions
Solution
‎02-27-2014 05:51 PM
Super User
Posts: 19,775

Re: sqlloop question

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;

View solution in original post


All Replies
Super User
Posts: 19,775

Re: sqlloop question

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.

Occasional Contributor
Posts: 13

Re: sqlloop question

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

Solution
‎02-27-2014 05:51 PM
Super User
Posts: 19,775

Re: sqlloop question

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;

Occasional Contributor
Posts: 13

Re: sqlloop question

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.

Occasional Contributor
Posts: 13

Re: sqlloop question

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);

                    -

                    22

ERROR: Undeclared array referenced: ifn.

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

                       -

                       22

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);

                          -

                          22

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

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

                          -

                          76

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

2782  end;

2783  run;

Super User
Posts: 19,775

Re: sqlloop question

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;

Occasional Contributor
Posts: 13

Re: sqlloop question

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!

Occasional Contributor
Posts: 13

Re: sqlloop question

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!

Super User
Posts: 19,775

Re: sqlloop question

Example of By group processing:

proc sort data=sashelp.class; by sex;

proc reg data=class;

by sex;

model weight=height age;

run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 291 views
  • 0 likes
  • 2 in conversation