BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
econdon
Calcite | Level 5

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

9 REPLIES 9
Reeza
Super User

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.

econdon
Calcite | Level 5

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

Reeza
Super User

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;

econdon
Calcite | Level 5

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.

econdon
Calcite | Level 5

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;

Reeza
Super User

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;

econdon
Calcite | Level 5

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!

econdon
Calcite | Level 5

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!

Reeza
Super User

Example of By group processing:

proc sort data=sashelp.class; by sex;

proc reg data=class;

by sex;

model weight=height age;

run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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