topic Updating multiple obs in BY group in Developers

Updating multiple obs in BY group

deleted_user — Tue, 26 Feb 2008 02:19:16 GMT

I am working with data that has multiple observations per my BY statement; but has unique variables with data that is always unknown and changing. I need to update one column for all occurrences of this data. I've tried using UPDATE and MERGE and have come to the conclusion that those will not work. Is there a command or option that enables me to do this without altering the data in the two source datasets?

This sample code is a simplified concept of what I'm trying to do. The desired output is that all observations for days 3 and 4 to equal 'weekend' regardless of the number of transactions that occur. I cannot easily change set1; or know the number of transactions that occur on any given day.

data set1;
do days = 1 to 10;
%let NumOfTrans = int(rand('POISSON', 2)+1);
do transaction = 1 to &NumOfTrans;
daytype = 'workday';
output;
end;
end;
run;
data set2;
do days = 3 to 4;
daytype = 'weekend';
output;
end;
run;
data set3;
UPDATE set1 set2;
BY days;
run;

Re: Updating multiple obs in BY group

Cynthia_sas — Tue, 26 Feb 2008 06:47:48 GMT

Hi:
Using a %LET statement may not be having the effect that you want. Essentially, at compile time (not execution time), the macro variable &NUMOFTRANS is being set to the string that is the function call. You can see this by turning on the SYMBOLGEN option and then examining the LOG for how &NUMOFTRANS was resolved:
[pre]
SYMBOLGEN: Macro variable NUMOFTRANS resolves to int(rand('POISSON', 2)+1)
[/pre]

If you want to use these functions to set the end point of your inner loop, you'd be just as well off using a data step variable. In this case, the value of X would change with each iteration through the outer loop.
[pre]
data set1;
do days = 1 to 10;

x = int(rand('POISSON', 2)+1);
putlog '-------> ' x=;

do transaction = 1 to x;
daytype = 'workday';
output;
end;
end;
run;

proc print data=set1;
title 'Set1';
run;

data set2;
do days = 3 to 4;
daytype = 'weekend';
output;
end;
run;

proc print data=set2;
title 'Set2';
run;
[/pre]

Output for SET1:
[pre]
Set1

Obs days x transaction daytype

1 1 2 1 workday
2 1 2 2 workday
3 2 2 1 workday
4 2 2 2 workday
5 3 3 1 workday
6 3 3 2 workday
7 3 3 3 workday
8 4 4 1 workday
9 4 4 2 workday
10 4 4 3 workday
11 4 4 4 workday
12 5 2 1 workday
13 5 2 2 workday
14 6 5 1 workday
15 6 5 2 workday
16 6 5 3 workday
17 6 5 4 workday
18 6 5 5 workday
19 7 2 1 workday
20 7 2 2 workday
21 8 3 1 workday
22 8 3 2 workday
23 8 3 3 workday
24 9 6 1 workday
25 9 6 2 workday
26 9 6 3 workday
27 9 6 4 workday
28 9 6 5 workday
29 9 6 6 workday
30 10 1 1 workday

[/pre]

The output for SET2:
[pre]
Set2

Obs days daytype
1 3 weekend
2 4 weekend
[/pre]

So if I understand what you want, you'd like to see ALL days 3 and 4 in SET1 changed to WEEKEND????? You could do that with a second data step program and an IF statement -- you don't need a MERGE or an UPDATE statement:
[pre]
data newset;
set set1;
if days = 3 or days = 4 then do;
daytype = 'weekend';
end;
run;
[/pre]

A PROC PRINT for the NEWSET dataset shows that only days=3 or days=4 have been changed:
[pre]
NEWSET from SET1

Obs days x transaction daytype

1 1 2 1 workday
2 1 2 2 workday
3 2 2 1 workday
4 2 2 2 workday
5 3 3 1 weekend
6 3 3 2 weekend
7 3 3 3 weekend
8 4 4 1 weekend
9 4 4 2 weekend
10 4 4 3 weekend
11 4 4 4 weekend
12 5 2 1 workday
13 5 2 2 workday
14 6 5 1 workday
15 6 5 2 workday
16 6 5 3 workday
17 6 5 4 workday
18 6 5 5 workday
19 7 2 1 workday
20 7 2 2 workday
21 8 3 1 workday
22 8 3 2 workday
23 8 3 3 workday
24 9 6 1 workday
25 9 6 2 workday
26 9 6 3 workday
27 9 6 4 workday
28 9 6 5 workday
29 9 6 6 workday
30 10 1 1 workday

[/pre]

Or, you could even just change your original program for SET1 to do this:
[pre]
data altset1;
do days = 1 to 10;
x = int(rand('POISSON', 2)+1);
putlog '-------> ' x=;

do transaction = 1 to x;
daytype = 'workday';
if days = 3 or days = 4 then do;
daytype = 'weekend';
end;
output;
end;
end;
run;

proc print data=altset1;
title 'AltSet1';
run;
[/pre]

With this version of the program, no matter how many transactions you have, ALL the transactions for days 3 and 4 will be set to WEEKEND and all the other days will be set to WEEKDAY.

At any rate, I'm not sure how this is related to stored processes. Perhaps you meant to post this in the other forum on macro variables and DATA step processing. If you have a particular need to use macro variables, then you might read about the %SYSFUNC macro function, which allows you to invokve data step functions to set the value of a macro variable. However, there are a couple of downsides to %SYSFUNC -- one thing is that you can't nest 2 %SYSFUNC calls. But I don't think you need it.

If you have other issues with creating a test data set or deciding how to use SAS functions, you might consider contacting Tech Support for help deciding how to create your test data set.

cynthia

Re: Updating multiple obs in BY group

deleted_user — Tue, 26 Feb 2008 15:38:22 GMT

maybe adding in some simiplified sample code only confused the situation.

So to begin, I added the %let and rand() only to quickly produce a random number to simulate the effect of having an unknown number of transactions per day.

Also the two data sets are much more complex than these and as I said before I have no ability to change set1 because of variability.

I am fairly familiar with SAS so simple solutions like an if statment would have been implemented and maybe implemented if it were feasible or become my only choice.

In general, I feel that if I am doing something that requires a lot of time and involved coding, I find that I am doing it the hard/wrong way. And it is likely that SAS already has a method of doing what i'd like to do. I'm fairly certain that method will look like an update or a merge, though obviously neither of those. I was just hoping that someone could point me in the right direction. Is there a 1-to-MANY merge/update method?

Re: Updating multiple obs in BY group

deleted_user — Tue, 26 Feb 2008 17:11:58 GMT

GOT IT! Since I had no ability to change set1 I looked at changing set2. I noticed that when I merge a new (not previously existing) variable to set1 that it merged for all occurences of the BY group. Then with an if statement after the merge I was able to assign the value to the proper variable and do some clean up. Thanks for your help. Here is what the example would look like...

data set1;
do days = 1 to 10;
%let NumOfTrans = int(rand('POISSON', 2)+1);
do transaction = 1 to &NumOfTrans;
daytype = 'workday';
output;
end;
end;
run;

data set2;
do days = 3 to 4;
daytypeholder = 'weekend';
output;
end;
run;

data set3;
merge set1 set2;
by days;
if daytypeholder = 'weekend' then daytype = 'weekend';
drop daytypeholder;
run; Message was edited by: Johnny.Key