Here is the My Sample code. I want to identify in X1 column group for 1 the X3 column values are 10,11 and 13.
Similarly when I will check for the other group in X1 column for group 2 if the X3 values are repeated for group 1 values then I have to replace with null. Could you please assist me.
data temp; input x1 x2 x3 x4$ x5$; cards; 1 2 10 AA BB 1 2 11 AA BB 1 2 13 AA BC 2 3 14 AB CD 2 3 11 AB CE 3 4 15 AC DF 4 5 16 AD EG 4 5 10 AD EH 5 6 11 AE FI 6 7 18 AF GJ 6 7 13 AF GK ; run;
Expecting output should be : 1 2 10 AA BB 1 2 11 AA BB 1 2 13 AA BC 2 3 14 AB CD 2 3 NULL AB CE 3 4 15 AC DF 4 5 16 AD EG 4 5 NULL AD EH 5 6 NULL AE FI 6 7 18 AF GJ 6 7 NULL AF GK
Please try the below code
proc sql noprint;
select x3 into: vals separated by ',' from temp where x1=1;
quit;
%put &vals;
data want;
set temp;
if x1 ne 1 and x3 in (&vals) then x3=.;
run;
Please try the below code
proc sql noprint;
select x3 into: vals separated by ',' from temp where x1=1;
quit;
%put &vals;
data want;
set temp;
if x1 ne 1 and x3 in (&vals) then x3=.;
run;
The data step does not have a null value. Do you want it to be zero or missing?
And what if the same group contains a duplicate like this?
1 2 10 AA BB 1 2 11 AA BB 1 2 10 AA BC
anything is fine zero or missing.
And what if a single group contains duplicates?
My 2 cents
data want;
declare hash h ();
h.definekey ("x3");
h.definedone();
do until (lr);
set temp end=lr;
if h.add() ne 0 then x3=.;
output;
end;
run;
Result
Obs x1 x2 x3 x4 x5 1 1 2 10 AA BB 2 1 2 11 AA BB 3 1 2 13 AA BC 4 2 3 14 AB CD 5 2 3 . AB CE 6 3 4 15 AC DF 7 4 5 16 AD EG 8 4 5 . AD EH 9 5 6 . AE FI 10 6 7 18 AF GJ 11 6 7 . AF GK
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: