Hi,
I have the following dataset:
data have; input col_a col_b :date9. col_c $ col_d $; format col_b date9.; datalines; 123 31SEP2014 123a 123456789ABC 123 31SEP2014 123a 123456789DEF 123 31SEP2014 123a 123456789GHI 456 31AUG2014 456f 987654321ABC 456 31AUG2014 456g 987654321DEF 456 31AUG2014 456h 987654321GHI 789 31OCT2014 789a 987654321ABC 789 31NOV2014 789a 987654321DEF 789 31NOV2014 789b 987654321GHI ; run;
Using this data set and (proc) sql I like to create a new column that holds a string of distinct col_c for each distinct combination of col_an and col_b. Like the following:
data have; input col_a col_b :date9. col_c $ col_d $ col_e; format col_b date9.; datalines; 123 31SEP2014 123a 123456789ABC 123_123a 123 31SEP2014 123a 123456789DEF 123_123a 123 31SEP2014 123a 123456789GHI 123_123a 456 31AUG2014 456f 987654321ABC 456_456f_456g_456h 456 31AUG2014 456g 987654321DEF 456_456f_456g_456h 456 31AUG2014 456h 987654321GHI 456_456f_456g_456h 789 31OCT2014 789a 987654321ABC 789_789a 789 31NOV2014 789a 987654321DEF 789_789a_789b 789 31NOV2014 789b 987654321GHI 789_789a_789b ; run;
Thank you in advance,
B
Not with SQL code. Just use plain old SAS code instead. Note: 30 days hath November, April...
data have;
input col_a col_b :date9. col_c $ col_d $;
format col_b date9.;
datalines;
123 30SEP2014 123a 123456789ABC
123 30SEP2014 123a 123456789DEF
123 30SEP2014 123a 123456789GHI
456 31AUG2014 456f 987654321ABC
456 31AUG2014 456g 987654321DEF
456 31AUG2014 456h 987654321GHI
789 31OCT2014 789a 987654321ABC
789 30NOV2014 789a 987654321DEF
789 30NOV2014 789b 987654321GHI
;
proc sort;
by col_a col_b col_c ;;
run;
data want ;
do _n_=1 by 1 until(last.col_b);
set have ;
by col_a col_b col_c ;
length col_e $50;
if first.col_c then col_e=catx('_',col_e,col_c);
end;
do _n_=1 to _n_;
set have;
output;
end;
run;
Results
Obs col_a col_b col_c col_d col_e 1 123 30SEP2014 123a 12345678 123a 2 123 30SEP2014 123a 12345678 123a 3 123 30SEP2014 123a 12345678 123a 4 456 31AUG2014 456f 98765432 456f_456g_456h 5 456 31AUG2014 456g 98765432 456f_456g_456h 6 456 31AUG2014 456h 98765432 456f_456g_456h 7 789 31OCT2014 789a 98765432 789a 8 789 30NOV2014 789a 98765432 789a_789b 9 789 30NOV2014 789b 98765432 789a_789b
Not with SQL code. Just use plain old SAS code instead. Note: 30 days hath November, April...
data have;
input col_a col_b :date9. col_c $ col_d $;
format col_b date9.;
datalines;
123 30SEP2014 123a 123456789ABC
123 30SEP2014 123a 123456789DEF
123 30SEP2014 123a 123456789GHI
456 31AUG2014 456f 987654321ABC
456 31AUG2014 456g 987654321DEF
456 31AUG2014 456h 987654321GHI
789 31OCT2014 789a 987654321ABC
789 30NOV2014 789a 987654321DEF
789 30NOV2014 789b 987654321GHI
;
proc sort;
by col_a col_b col_c ;;
run;
data want ;
do _n_=1 by 1 until(last.col_b);
set have ;
by col_a col_b col_c ;
length col_e $50;
if first.col_c then col_e=catx('_',col_e,col_c);
end;
do _n_=1 to _n_;
set have;
output;
end;
run;
Results
Obs col_a col_b col_c col_d col_e 1 123 30SEP2014 123a 12345678 123a 2 123 30SEP2014 123a 12345678 123a 3 123 30SEP2014 123a 12345678 123a 4 456 31AUG2014 456f 98765432 456f_456g_456h 5 456 31AUG2014 456g 98765432 456f_456g_456h 6 456 31AUG2014 456h 98765432 456f_456g_456h 7 789 31OCT2014 789a 98765432 789a 8 789 30NOV2014 789a 98765432 789a_789b 9 789 30NOV2014 789b 98765432 789a_789b
Thanks!
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: