How to order header variables in correct sequence after transposing

[wlierman]

I have a dataset with 3.5 million obs and only three variables: recipient_ID,

claim, month (in form like '201704' for example)

Data Stg_Out4.PMCA_Claims_table_3 (drop = tcn month);
	Set Stg_Out4.Claims_table_2_2019;
	By recipno tcn;
	retain Recipno Month
                      Claim_201607 Claim_201608 Claim_201609 Claim_201610
	              Claim_201611 Claim_201612 Claim_201701 Claim_201702
                      Claim_201703 Claim_201704 Claim_201705 Claim_201706;

	Array clmmon (12) Claim_201607 Claim_201608 Claim_201609                       Claim_201610 Claim_201611 Claim_201611 Claim_201612 Claim_201701 Claim_201702
                      Claim_201703 Claim_201704 Claim_201705 Claim_201706;
Run;

I have executed proc transpose to put the claim_month as a header line.  I created an array structure (see code above). 

I have created flags ('1') for the months that had a claim by each recipient in this step:

/** Create flags **/
Data Stg_Out4.Claims_flags_2019;
  Set Stg_Out4.PMCA_Claims_table_3;
  by Recipno;
  
  If Claim_201607 ne '.' then Claim_201607 = '1';
  else If Claim_201608 ne '.' then Claim_201608 = '1';
  else If Claim_201609 ne '.' then Claim_201609 = '1';
  else If Claim_201610 ne '.' then Claim_201610 = '1';
  else If Claim_201611 ne '.' then Claim_201611 = '1';
  else If Claim_201612 ne '.' then Claim_201612 = '1';
  else If Claim_201701 ne '.' then Claim_201701 = '1';
  else If Claim_201702 ne '.' then Claim_201702 = '1';
  else If Claim_201703 ne '.' then Claim_201703 = '1';
  else If Claim_201704 ne '.' then Claim_201704 = '1';
  else If Claim_201705 ne '.' then Claim_201705 = '1';
  else If Claim_201706 ne '.' then Claim_201706 = '1';
run;

So my question (likely very simple) when I open the flagged data set. The header line is not in calendar order instead it is:

Claim_201607  Claim_201608 Claim_201609  Claim_201612 CLAIM_201704 Claim_201705 Claim_201706 Claim201610

Claim_201611 Claim_201701 Claim_201702  Claim_201703    instead of the correct order.

Can you please show the corrected approach to get the header labels in proper order.  Is the fix universal in that it will work for other label types?

Thank you for your assistance.

Wlierman

[PaigeMiller]

The order of the variables shouldn't matter at all. Why would you want to do actual work to correct this?

[wlierman]

Hello,

The fiscal year runs 201607 to 201706.  So subsequent calculations and results need to be reported in calendar order, otherwise some readers will be surprised and likely not understand.

Thanks.

[PaigeMiller · Accepted solution]

As always, details on how you are going to do this are important. If you are just creating an Excel spreadsheet for example, then using ODS EXCEL you simply name the columns in the order that you want them to be in (which by the way is a lot easier than trying to program it to work in every case, because you have already typed the column names in the desired order).

[PaigeMiller]

Adding: TRANSPOSE seems to be the problem, and also unnecessary here. PROC REPORT will put the months in the proper order, without you doing any transpose at all.

[wlierman]

I needed to transpose since I had a narrow (3 vars) and long (3.5 million rows) and need a wide data set.

After transposing I have a count of claims for each recipient on one line.

Then I am joining this data set to a data set with body system counts for a subset of the recipients.

Thanks.

[PaigeMiller]

Well, its still not clear why the long data set doesn't work,, what are you going to do after you get this wide data set merged with something else?

In other words, don't tell me small portions of the problem, tell me the final goal. Is it an analysis, or a report, or an Excel file? What should it look like?

[Tom]

Your words and program code don't match up.  You said the data has only three columns and show a data step that is referencing many columns.

Assuming that you actually used PROC TRANSPOSE then the issue is probably from the fact that PROC TRANSPOSE creates the columns as it sees values for them.  So if the first BY group does not have all of the values of the ID variable then the result is like you describe.  There is a simple trick to force PROC TRANSPOSE make the columns in the order you want. Give it the ID values in the order you want.  You can do this be adding an extra group.  

proc sql ;
create table all_months as 
select distinct month 
from HAVE 
order by month
;
quit;

data for_transpose / view=for_transpose ;
  set all_months have;
  present=1;
run;

proc transpose data=for_tranpose  prefix=month_
out=want (drop=_name_ where=(not missing(recipno)))
;
  by recipno tcn;
  id month;
  var present;
run;