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