Hi
I have a dataset that looks like this:
ID | IN | Type | Sum |
1 | 30000 | 1 | 45000 |
1 | 15000 | 3 | 45000 |
2 | 4000 | 3 | 4000 |
3 | 500 | 1 | 10500 |
3 | 10000 | 8 | 10500 |
4 | 3000 | 1 | 17300 |
4 | 6000 | 3 | 17300 |
4 | 7000 | 3 | 17300 |
4 | 300 | 6 | 17300 |
5 | 40000 | 8 | 40000 |
6 | 2000 | 1 | 9400 |
6 | 3200 | 3 | 9400 |
6 | 4200 | 8 | 9400 |
and I would like to create a dummy variable for the whole group of observations so that if type=8 then all observations with the same ID have the same dummy. It would look something like this:
ID | IN | Type | Sum | D |
1 | 30000 | 1 | 45000 | 0 |
1 | 15000 | 3 | 45000 | 0 |
2 | 4000 | 3 | 4000 | 0 |
3 | 500 | 1 | 10500 | 1 |
3 | 10000 | 8 | 10500 | 1 |
4 | 3000 | 1 | 17300 | 0 |
4 | 6000 | 3 | 17300 | 0 |
4 | 7000 | 3 | 17300 | 0 |
4 | 300 | 6 | 17300 | 0 |
5 | 40000 | 8 | 40000 | 1 |
6 | 2000 | 1 | 9400 | 1 |
6 | 3200 | 3 | 9400 | 1 |
6 | 4200 | 8 | 9400 | 1 |
Thank you in advance!
If you provide test data as a datastep (so I don't have to type all that in) then I can provide working code, as it is this is guess not tested:
proc sql; create table WANT as select A.*, case when B.D=1 then 1 else 0 end as D from HAVE A left join (select distinct ID,1 from HAVE where ID=A.ID and TYPE=8) B on A.ID=B.ID; quit;
Sure
data have;
input ID IN Type Sum;
datalines;
1 30000 1 45000
1 15000 3 45000
2 4000 3 4000
3 500 1 10500
3 10000 8 10500
4 3000 1 17300
4 6000 3 17300
4 7000 3 17300
4 300 6 17300
5 40000 8 40000
6 2000 1 9400
6 3200 3 9400
6 4200 8 9400
;
run;
So yes:
data have; input ID IN Type Sum; datalines; 1 30000 1 45000 1 15000 3 45000 2 4000 3 4000 3 500 1 10500 3 10000 8 10500 4 3000 1 17300 4 6000 3 17300 4 7000 3 17300 4 300 6 17300 5 40000 8 40000 6 2000 1 9400 6 3200 3 9400 6 4200 8 9400 ; run; proc sql; create table WANT as select A.*, case when B.D=1 then 1 else 0 end as D from HAVE A left join (select distinct ID,1 as D from HAVE where TYPE=8) B on A.ID=B.ID; quit;
Hi @AnnaKM,
If your input dataset is sorted by ID, you could also use a data step (DOW loop technique) to achieve the result:
data want;
do until(last.ID);
set have;
by ID;
if first.ID then D=0;
if type=8 then D=1;
end;
do until(last.ID);
set have;
by ID;
output;
end;
run;
Both solutions worked really well. Thank you!
data have; input ID IN Type Sum; datalines; 1 30000 1 45000 1 15000 3 45000 2 4000 3 4000 3 500 1 10500 3 10000 8 10500 4 3000 1 17300 4 6000 3 17300 4 7000 3 17300 4 300 6 17300 5 40000 8 40000 6 2000 1 9400 6 3200 3 9400 6 4200 8 9400 ; run; proc sql; create table want as select *,(sum(Type=8) gt 0) as D from have group by id; quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.