data have;
length catgy $25 month_ $5 tot_cnt 8 descr $25 descr_pct monthsum 8
tran_type $3 seq_id 8 ;
input catgy -- seq_id;
datalines;
CURRENT JAN20 33 STAYED 98 1 D 3
CURRENT JAN20 33 ROLLED 2 1 D 3
CURRENT FEB20 25 STAYED 2 2 D 3
CURRENT FEB20 25 ROLLED 4 2 D 2
;
run;
Produces the following | |||||||||||
catgy | month_ | tot_cnt | descr | descr_pct | monthsum | tran_type | seq_id | ||||
CURRENT | JAN20 | 33 | STAYED | 98 | 1 | D | 3 | ||||
CURRENT | JAN20 | 33 | ROLLED | 2 | 1 | D | 3 | ||||
CURRENT | FEB20 | 25 | STAYED | 2 | 2 | D | 3 | ||||
CURRENT | FEB20 | 25 | ROLLED | 4 | 2 | D | 2 | ||||
Desired result is to add a new row, place an F under tran_type and place the total count of the records under seq_id | |||||||||||
catgy | month_ | tot_cnt | descr | descr_pct | monthsum | tran_type | seq_id | ||||
CURRENT | JAN20 | 33 | STAYED | 98 | 1 | D | 3 | ||||
CURRENT | JAN20 | 33 | ROLLED | 2 | 1 | D | 3 | ||||
CURRENT | FEB20 | 25 | STAYED | 2 | 2 | D | 3 | ||||
CURRENT | FEB20 | 25 | ROLLED | 4 | 2 | D | 2 | ||||
F | 4 |
This works for your example:
data want; set have end=last curobs=obsnum; output; if last then do; call missing (of catgy-- monthsum); seq_id=obsnum ; Tran_type='F'; output; end; run;
End option creates a temporary variable that is set to 1 (true) when the current observation is last.
The Curobs option creates a temporary variable with the observation number, i.e. the row number in the data.
Call Missing, if you have not encountered it, sets a list of variables to missing values. It is one of the few functions that works on both character and numeric values.
This works for your example:
data want; set have end=last curobs=obsnum; output; if last then do; call missing (of catgy-- monthsum); seq_id=obsnum ; Tran_type='F'; output; end; run;
End option creates a temporary variable that is set to 1 (true) when the current observation is last.
The Curobs option creates a temporary variable with the observation number, i.e. the row number in the data.
Call Missing, if you have not encountered it, sets a list of variables to missing values. It is one of the few functions that works on both character and numeric values.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.