proc sort data = dataset1 nodupkey; by timepoint; run;     ... View more

I don't think you have to use the first. and last. steps. data test2; set test1; by status category; retain numcat 0; if status = 'Open' then numcat+1; else numcat=0; cat = numcat; keep status category cat numcat; run;    this will get you required output. ... View more

Just run this query,   data test1; input part_code$ miami la; cards; A_1 60000 38000 A_2 5000 2000 A_3 1000 60000 ; run;   proc transpose data = test1 out = test2; by part_code; var miami la; run;   data test3; input part_code$ city$; cards; A_1 Miami A_1 LA A_2 Miami A_2 LA ; run;   proc sql; create table test4 as select distinct a.* from test2 a left join test3 b on a.part_code = b.part_code; quit; ... View more

why don't you use the function count() instead of freq??  ... View more

/*Few cells are missing in the original data, I replaced them with null values*/ data Customer; infile datalines missover; input CustomerId$ DomicileCountryCode $ IncorporationCountryCode $ ParentCustomerId $ CustomerType $; datalines; A1111 SG . X1111 BANK B2222 HK . X1111 BANK X1111 US US . . X2222 TW TW . . C3333 CN . X2222 CORP ; run; data Scorecard (where=(ScoreCardApprovedFlag='Y')); input CustomerId $ ScoreCardId CustomerCreditGrade $ ScoreCardDate ScoreCardApprovedFlag $; informat ScoreCardDate ddmmyy10.; format ScoreCardDate ddmmyy10.; cards; X1111 32104 AA+ 31/12/2016 Y X1111 32105 AAA 31/03/2017 N X1111 32103 AA 30/6/2016 Y X1111 32102 AA- 31/03/2016 Y X2222 40321 BBB 31/12/2016 N X2222 40322 BBB+ 31/12/2016 Y ; run; proc sort data = Scorecard ; by CustomerId descending ScoreCardDate ; run; proc sort data =Scorecard nodupkey; by customerid; run; /*Manually passing the values of incorporation country code, since they are not given for all the customers*/ data Scorecard; set Scorecard; if customerid= 'X1111' then IncorporationCountryCode = 'US'; if customerid= 'X2222' then IncorporationCountryCode = 'TW'; run; data customer2; set Customer; new=COALESCEc(ParentCustomerId,CustomerId); run; proc sql; create table final as select a.CustomerId, b.ScoreCardId, b.CustomerCreditGrade, a.DomicileCountryCode, b.IncorporationCountryCode from customer2 a left join Scorecard b on a.new = b.customerid order by 1; quit; ... View more