data have1; input x y; cards; 1 2 3 4 ; run; data have2; input r s; cards; 5 6 7 8 9 0 ; run; data combine; set have1 have2; run; data combine creates a four column dataset. is there a way to stack have1 and have2 so that have2 is underneath have1 despite having different variable names. Objective is to only have 2 columns ... View more

I am unable to replicate this in a test dataset however please look at this. have is a proc transpose. Notice the cascading variables that end in PCT. Is there a way to rename the four in _NAME_ to something like Stay_Curr and let all entries appear on the same row in order as seen in the want-proc transpose (or some other routine)?? have - proc transpose Group Category _NAME_ DEC20 JAN21 FEB21 MAR21 APR21 Current Count Count_ 736422 730405 722577 721558 704774 Current Count _04_Stay_Pct 0.94515373 1 1 1 1 Current Count _03_Stay_Pct 1 0.951110685 1 1 1 Current Count _02_Stay_Pct 1 1 0.951131852 1 1 Current Count _01_Stay_Pct 1 1 1 0.95324423 1 Current Count _00_Stay_Pct 1 1 1 1 0.956489598 want-proc transpose Group Category _NAME_ DEC20 JAN21 FEB21 MAR21 APR21 Current Count Count_ 736422 730405 722577 721558 704774 Current Count Stay_Curr_Pct 0.94515373 0.951110685 0.951131852 0.95324423 0.956489598 ... View more

data have; input Mgr $ Exception $ CNT ; datalines; Smith Transfer 1 Smith Transfer 1 Smith Cancel 1 Brown Reinstate 1 Brown Terminate 1 Saunders Transfer 1 Saunders Cancel 1 ; run; proc sql; create table have2 as select Mgr,Exception,sum(cnt) as tot_cnt from have group by Mgr,Exception ;quit; proc transpose data=have2 out=want let; id Exception; var tot_cnt; by Mgr; run;   1. Is it possible to rename tot_cnt as My_Count 2.  If I had multiple var could I rename each var as desired? ... View more

data have1; format bi_date date9.; infile datalines; input ln_no $4. days_past1 bi_date date9. tot_cnt ; return; datalines; 1111 12 31Mar2020 1 2122 12 31Mar2020 1 1244 12 31Mar2020 1 3333 44 31Mar2020 1 2222 30 31Mar2020 1 ;run; data have2; format bi_date date9.; infile datalines; input ln_no $4. days_past2 bi_date date9. tot_cnt ; return; datalines; 1111 22 30Apr2020 1 2122 32 30Apr2020 1 1244 31 30Apr2020 1 3333 30 30Apr2020 1 2222 13 30Apr2020 1 ;run; proc sort data=have1;by ln_no;run; proc sort data=have2;by ln_no;run; data merge_all; merge have1 have2; by ln_no; run; /***********************************/ produces the following Category bi_date1 ln_no days_past1 tot_cnt bi_date2 days_past2 Current 31Mar2020 1111 12 1 30Apr2020 22 Curr_Past_Due 31Mar2020 1244 12 1 30Apr2020 31 Curr_Past_Due 31Mar2020 2122 12 1 30Apr2020 32 Current 31Mar2020 2222 30 1 30Apr2020 13 Past_to_Current 31Mar2020 3333 44 1 30Apr2020 30   I attempting to do a pivot report however proc transpose only captures the first instance of date statistics.  I am thinking a proc report may do the trick but wanted input.  Here is the desired output Category 31-Mar-20 30-Apr-20 Pct_Chg Current 1 1 0% Curr_Past_Due 1 1 0% Past_to_Current 1 0 50% Grand Total 3 2   ... View more

  data have; format bi_date date9.; infile datalines; input ln_no $4. days_past bi_date date9. tot_cnt stay_current ; return; datalines; 1111 12 31Mar2020 1 1 2122 12 31Mar2020 1 1 1244 12 31Mar2020 1 1 3333 44 31Mar2020 1 0 2222 30 31Mar2020 1 0 1111 20 30Apr2020 1 1 2222 20 30Apr2020 1 1 3333 22 30Apr2020 1 1 1111 30 31May2020 1 0 2222 11 31May2020 1 1 3333 45 31May2020 1 0 ;run;   proc sql; create table _01_count as select 'Current' as lbl_Current,sum(tot_cnt) as Count, sum(stay_current) as Stayed_Current, bi_date from have group by bi_date order by bi_date ;quit;   proc transpose data=_01_count out=_01_count_2 let; id bi_date; var count Stayed_Current; by lbl_Current; run; Produces this lbl_Current _NAME_ 31MAR2020 30APR2020 31MAY2020 Current Count 5 3 3 Current Stayed_Current 3 3 1   Desired Result as follows.  I want to change the dates to char fields however keep the date order I want to display the Count in it’s current form as a numeric however display the Stayed_Current as a Pct of the totals lbl_Current _NAME_ 31MAR2020 30APR2020 31MAY2020 Current Count 5 3 3 Current Stayed_Current 60.00% 100.00% 33.33%   Would I need to handle the calculation in the proc sql,proc tab or in the datastep ... View more

proc sql; create table columns as select name as variable ,memname as table_name from dictionary.columns where libname = 'LAKEP' /*and user_id in ('1LB','402','CM7','EI9','JS2','J8M', 'KS7','MB2','NR4','VG5') or username in ('1LB','402','CM7','EI9','JS2','J8M', 'KS7','MB2','NR4','VG5')*/ ; quit;   The above code will return the variable columns for a particular libname.  I know you cannot run this code however as a general question, is it possible to do a query of an entire libname and return the contents of a particular variable?  As you can I commented out user_id and username.  i am trying to determine If I can isolate particular user_id.  Currently I am performing dataset queries on each individual table.   ... View more

data have; length borr_ph $10 co_borr_ph $10 alt_ph $10 ; input borr_ph -- alt_ph; datalines; 8042234314 0000000000 3334444444 0000000000 1111111111 9999999999 2132245566 1121111111 2321111111 1111114444 0000000000 1111111111 1112223333 8042342256 0000000000 ; run; data want; set have; if substr(borr_ph,1,4) in('0000','1111','2222','3333','4444','9999') then no_borr_ph='Y'; if substr(co_borr_ph,1,4) in('0000','1111','2222','3333','4444','9999') then no_co_borr_ph='Y'; if substr(alt_ph,1,4) in('0000','1111','2222','3333','4444','9999') then no_alt_ph='Y'; if no_borr_ph ='Y' and no_co_borr_ph='Y' and no_alt_ph = 'Y' then No_Phone = 'Y'; run;   I used a substr analysis to try and identify phantom phone numbers (numbers that obviously are bogus)  I successfully flagged those with the phantom or non-phone number. My actual code has about 20 different phone numbers.  Is there a way to apply some type of array so I do not have to repeat the entire sequence of code as seen here. Note the reason I am using a sequence of first four numbers is because it is unlikely that a phone number begin with the same 4 numbers consecutive ... View more