Dear all, if I have a data that looks like this
data have;
infile datalines;
input id var1 var2 var3 var4 $3.;
datalines;
1 4 3 2 5
2 2 4 5 6
3 4 6 7 8
4 8 4 2 5
5 6 1 0 10
6 10 1 4 5
7 4 5 6 4
8 1 4 6 4
9 5 5 9 10
10 4 9 4 4
;
run;
I want each time the value of var1=4 occurs for the first time, all values of subsequent variables to be outputted . e.g. for id=1, the output will be 3, 2, 5
id=2, will be 5 and 6 etc.
Any help?
Ok. Try this
data have;
infile datalines;
input id (var1 - var4)($);
datalines;
1 4 3 2 5
2 2 4 5 6
3 4 6 7 8
4 8 4 2 5
5 6 1 0 10
6 10 1 4 5
7 4 5 6 4
8 1 4 6 4
9 5 5 9 10
10 4 9 4 4
;
run;
data want(drop = flag);
set have;
array v $ var:;
if '4' in v;
do over v;
if v = '4' then flag = 1;
if flag = . then v = '';
end;
run;
Result:
id var1 var2 var3 var4 1 4 3 2 5 2 4 5 6 3 4 6 7 8 4 4 2 5 6 4 5 7 4 5 6 4 8 4 6 4 10 4 9 4 4
Outputted how? Can you post the form of your desired result?
data want should look like this
id | var1 | var2 | var3 | var4 |
1 | 4 | 3 | 2 | 5 |
2 | 4 | 5 | 6 | |
3 | 4 | 6 | 7 | 8 |
4 | 4 | 2 | 5 | |
6 | 4 | 5 | ||
7 | 4 | 5 | 6 | 4 |
8 | 4 | 6 | 4 | |
10 | 4 | 9 | 4 | 4 |
Ok. Is var4 character and the other vars numeric intentionally?
sorry, var1-var4 are characters only the id is numeric
Ok. Try this
data have;
infile datalines;
input id (var1 - var4)($);
datalines;
1 4 3 2 5
2 2 4 5 6
3 4 6 7 8
4 8 4 2 5
5 6 1 0 10
6 10 1 4 5
7 4 5 6 4
8 1 4 6 4
9 5 5 9 10
10 4 9 4 4
;
run;
data want(drop = flag);
set have;
array v $ var:;
if '4' in v;
do over v;
if v = '4' then flag = 1;
if flag = . then v = '';
end;
run;
Result:
id var1 var2 var3 var4 1 4 3 2 5 2 4 5 6 3 4 6 7 8 4 4 2 5 6 4 5 7 4 5 6 4 8 4 6 4 10 4 9 4 4
Thanks this was very fast, will this also work if var were to be numeric?
If they were all numeric, then yes, but of course, you would have to change '4' to 4 🙂
yes, I tried that and it also worked, thanks
Anytime.
@PeterClemmensen sorry I still need to ask this question for feature use. In case I need for this output not only the subsequent values but also the previous values how can I add this in the code?
Meaning you want to blank all values besides the first one that is =4 / '4' ?
meaning that I want in the output also the values of the blanks to show.
and secondly, probably I wouldn't like to display the first occurence of 4 in the results
it should then look like this:
so that I can later concatenate easily;
id | var1 | var2 | var3 | var4 |
1 | 3 | 2 | 5 | |
2 | 2 | 5 | 6 | |
3 | 6 | 7 | 8 | |
4 | 8 | 2 | 5 | |
6 | 10 | 1 | 5 | |
7 | 5 | 6 | 4 | |
8 | 1 | 6 | 4 | |
10 | 9 | 4 | 4 |
This blanks simply all the '4' values?
sorry, I didn't understand your question. I was thinking of blanking all the first '4' values not all the '4' values and outputting also the preceeding values
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.