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;
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.
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.