Dear All:
May data set is as follows
Date ID VarA Interval
01JAN 2019 A 22 1
01JAN 2019 A 26 1
01JAN 2019 A 21 1
01JAN 2019 A 24 2
01JAN 2019 A 27 2
01JAN 2019 B 19 1
01JAN 2019 B 23 1
01JAN2019 B 16 7
02JAN 2019 A 95 1
02JAN 2019 A 62 1
02JAN 2019 A 21 1
02JAN 2019 A 43 2
02JAN 2019 A 51 2
02JAN 2019 B 33 1
02JAN 2019 B 45 5
02JAN2019 B 56 5
I want my data to be as follows:
Date ID Interval VarA_FO VarA_LO
01JAN 2019 A 1 22 21
01JAN 2019 A 2 24 27
01JAN 2019 B 1 19 23
01JAN2019 B 7 16 16 /* FO = LO */
02JAN 2019 A 1 95 21
02JAN 2019 A 2 43 51
02JAN 2019 B 1 33 33 /* FO = LO */
02JAN 2019 B 5 45 56
Thanks in advance.
Randy
Seems simple. You will need to RETAIN the value from the first observation.
data have;
input date :date. id $ vara interval ;
format date date9.;
cards;
01JAN2019 A 22 1
01JAN2019 A 26 1
01JAN2019 A 21 1
01JAN2019 A 24 2
01JAN2019 A 27 2
01JAN2019 B 19 1
01JAN2019 B 23 1
01JAN2019 B 16 7
02JAN2019 A 95 1
02JAN2019 A 62 1
02JAN2019 A 21 1
02JAN2019 A 43 2
02JAN2019 A 51 2
02JAN2019 B 33 1
02JAN2019 B 45 5
02JAN2019 B 56 5
;
data want;
set have;
by date id interval;
if first.interval then vara_fo=vara;
retain vara_fo;
if last.interval;
vara_lo=vara;
drop vara ;
run;
OBS date id interval vara_fo vara_lo 1 01JAN2019 A 1 22 21 2 01JAN2019 A 2 24 27 3 01JAN2019 B 1 19 23 4 01JAN2019 B 7 16 16 5 02JAN2019 A 1 95 21 6 02JAN2019 A 2 43 51 7 02JAN2019 B 1 33 33 8 02JAN2019 B 5 45 56
data have;
infile cards;
input Date : date9. ID $ VarA Interval;
cards;
01JAN2019 A 22 1
01JAN2019 A 26 1
01JAN2019 A 21 1
01JAN2019 A 24 2
01JAN2019 A 27 2
01JAN2019 B 19 1
01JAN2019 B 23 1
01JAN2019 B 16 7
02JAN2019 A 95 1
02JAN2019 A 62 1
02JAN2019 A 21 1
02JAN2019 A 43 2
02JAN2019 A 51 2
02JAN2019 B 33 1
02JAN2019 B 45 5
02JAN2019 B 56 5
;;;;
run;
proc sort data=have;
by id interval date;
run;
data fo_lo;
set have;
by id interval date;
if first.interval and last.interval then do;
Type = 'FO';
output;
Type = 'LO';
output;
end;
else do;
if first.interval then type = 'FO';
if last.interval then type = 'LO';
output;
end;
run;
proc transpose data=fo_lo out=want;
where not missing(type);
by id interval date;
var vara;
id type;
run;
@RandyStan wrote:
Dear All:
May data set is as follows
Date ID VarA Interval
01JAN 2019 A 22 1
01JAN 2019 A 26 1
01JAN 2019 A 21 1
01JAN 2019 A 24 2
01JAN 2019 A 27 2
01JAN 2019 B 19 1
01JAN 2019 B 23 1
01JAN2019 B 16 7
02JAN 2019 A 95 1
02JAN 2019 A 62 1
02JAN 2019 A 21 1
02JAN 2019 A 43 2
02JAN 2019 A 51 2
02JAN 2019 B 33 1
02JAN 2019 B 45 5
02JAN2019 B 56 5
I want my data to be as follows:
Date ID Interval VarA_FO VarA_LO
01JAN 2019 A 1 22 21
01JAN 2019 A 2 24 27
01JAN 2019 B 1 19 23
01JAN2019 B 7 16 16 /* FO = LO */
02JAN 2019 A 1 95 21
02JAN 2019 A 2 43 51
02JAN 2019 B 1 33 33 /* FO = LO */
02JAN 2019 B 5 45 56
Thanks in advance.
Randy
Seems simple. You will need to RETAIN the value from the first observation.
data have;
input date :date. id $ vara interval ;
format date date9.;
cards;
01JAN2019 A 22 1
01JAN2019 A 26 1
01JAN2019 A 21 1
01JAN2019 A 24 2
01JAN2019 A 27 2
01JAN2019 B 19 1
01JAN2019 B 23 1
01JAN2019 B 16 7
02JAN2019 A 95 1
02JAN2019 A 62 1
02JAN2019 A 21 1
02JAN2019 A 43 2
02JAN2019 A 51 2
02JAN2019 B 33 1
02JAN2019 B 45 5
02JAN2019 B 56 5
;
data want;
set have;
by date id interval;
if first.interval then vara_fo=vara;
retain vara_fo;
if last.interval;
vara_lo=vara;
drop vara ;
run;
OBS date id interval vara_fo vara_lo 1 01JAN2019 A 1 22 21 2 01JAN2019 A 2 24 27 3 01JAN2019 B 1 19 23 4 01JAN2019 B 7 16 16 5 02JAN2019 A 1 95 21 6 02JAN2019 A 2 43 51 7 02JAN2019 B 1 33 33 8 02JAN2019 B 5 45 56
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.