BookmarkSubscribeRSS Feed
deleted_user
Not applicable
The following code captures betaY1 values for all observations where varnum=1. It works, however, it only obtains the values for the first variable(varnum=1) and I would like it to cycle through all varnums successively and put the values for each group into a macro variable. i.e. a bmax macro variable containing the relevant betaY1 values for each varnum. I then intend to put the value of the macro variable into the betaval variable for the nth observation of each varnum and merge by varnum into the primary data containing observations 1 - (n-1).

Any suggestions?


proc sql noprint;
select betaY1 into :bmax separated by ','
from y_1
where varnum=1
;
quit;

%put &bmax;
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Why macro language (variable)? Remember, value resolution occurs at compilation time, not at the DATA step execution level. You may want to consider some other DATA step technique to interrogate your detail data, creating an extract data file, and then merge back this new file with your original file based on some logic.

If you need further guidance, suggest you post a data sample (both input and desired output / result) as a reply to your forum post.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
The number of betaval values will vary but there are some with as many as 9 rows associated with them. In that case each of eight betaval values would be needed for the value of the ninth betaval as it is the sum of all those preceding it for that variable.

I thought the sql into : might accomplish the goal and avoid the need for an array.

The betaY1 value(for valnum 1-8) has been assigned in a prior sql step. Variable reg2 has 9 possible values as identified by maxcnt. It is the betaY1 for the 9th value of reg2 that I am attempting to assign as above. Its value should be the neg. sum of all preceding values of reg2. (e.g.for valnum=9 betaY1 =NEG(B9+B11B13+B15+B17+B19+B21+B23). In general, # of values for any one variable(varT below) over the dataset is not consistent, which is to say that some variables have 9 possible values, some 4 values, some 2 values, etc. in each case the nth value will be assigned a betaY1 value that is the negative sum of the first n-1 values. I have created only numerical refernces to the beta value , not B1 - simply 1 for convenience...but these are only a reference so I cannot perform the calculation in this step only create the reference to the neg. sum. by listing the betaY1s of interest. The into : code results in 9,11,13,15,17,19,21,23 stored as a macro variable. I'm not happy about it and would welcome another solution.

value varT varnum valnum maxcnt intval betaY1

AZ reg2 3 1 9 2 9
CA reg2 3 2 9 2 11
FL reg2 3 3 9 2 13
GA reg2 3 4 9 2 15
MD reg2 3 5 9 2 17
MI reg2 3 6 9 2 19
NV reg2 3 7 9 2 21
VA reg2 3 8 9 2 23
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Focus on what you need to achieve with your data, before you decide on a technique within the SAS language.

Consider different SAS programming techniques available to you, such as MERGE, using a SAS format (for argument search/match), generating some "derived analysis" (a roll-up summary, last/first occurence, PROC analysis output, etc.) and applying that data back against your detail data with a MERGE to propagate / spread data-points based on some key (BY stmt) variables.

Most of all, don't get hung up on using macro variables, just because. Gotta look at the bigger picture and develop one or more potential programming scenarios and work through each one to come to a preferred technique that generates the expected results.

Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
Look down below Scott! I did it! Thanks for the push. Here's how I did it.


%macro create(howmany);
data total;
set &status._tot;
by varnum;
if first.varnum then valnum=0;
valnum+1;
run;


data tempcnt(keep=varnum valnum);
set total;
by varnum valnum;
if last.varnum eq 1 then output;
run;

data total;
merge total tempcnt(rename=(valnum=maxcnt));
by varnum;
run;

%do i=1 %to &howmany;
data &status._y_&i;
set total;
by varnum valnum;intval=&howmany;
if _n_=1 then betaY&i=&i;
if last.varnum ne 1 or sum(last.varnum,first.varnum) eq 2
then do;
betaY&i=betaY&i + intval;
retain betaY&i;
output;
end;
%end;
PUTLOG '>DIAG00' /_ALL_;
run;

*proc print data=trans1_y_1;run;

***re-read original TOTAL data set to pull the nth observation for each variable;
***set betaval as the cumulative betaval of values preceding it(for like variables);

data &status.nthval;
set total;
by varnum valnum;
if last.varnum eq 1 and sum(last.varnum,first.varnum) ne 2;
PUTLOG '>DIAG00' /_ALL_;
run;

proc print data=&status.nthval;
run;

data downsize(keep = varnum betay1);
set trans1_y_1;*&status._y_&i; ***this is causing the problem i is not defined outside the loop***
run;

proc transpose data=downsize out=betaside;
by varnum;
run;

options missing='';
data concat;
set betaside(drop=_name_);
sp=',';
array col{*} col:;
do i=1 to dim(col);
betay1=catx(sp,of col:);
end;

run;

data &status.nthval;
merge &status.nthval(in=a) concat(in=b keep=varnum betay1);
by varnum;
if a and b;
run;

proc print data=&status.nthval;
run;
%mend create;


varnum valnum maxcnt betay1
1 3 3 3,5
2 2 2 7
3 9 9 9,11,13,15,17,19,21,23
4 3 3 25,27
6 2 2 31
7 2 2 33
8 3 3 35,37
9 5 5 39,41,43,45
10 3 3 47,49
11 6 6 51,53,55,57,59
12 7 7 61,63,65,67,69,71
13 4 4 73,75,77


I still have to merge it back in with the rest of the observation but I've cleared the hurdle.

I'll have more questions tomorrow. new day.

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
  • 4 replies
  • 655 views
  • 0 likes
  • 2 in conversation