Creating Proc Transpose to avoid ID variable occurring twice

[wlierman]

I have included a small sample of data that I would like to transpose.  The following log contains the 20 obs.

NOTE: The infile _CODE_ is:
      Filename=D:\Data\OR0206965\SASWORK\_TD51864_WPOHAAPPL87_\#LN00417,
      RECFM=V,LRECL=32767,File Size (bytes)=1541,
      Last Modified=03Sep2021:14:02:18,
      Create Time=03Sep2021:14:02:18

data work.Arias_CBOs_with_Contacts_3 ;
  infile datalines dsd dlm='|' truncover;
  input CBO :$44. Jurisdiction :$14. Tracers Contacts Month :$14.
    Year :$14.
  ;
  format CBO $44. Jurisdiction $14. Tracers best. Contacts best. ;
  informat CBO $44. Jurisdiction $14. ;
  label CBO='CBO' Jurisdiction='Jurisdiction' Tracers='Tracers'
    Contacts='Contacts'
  ;
datalines4;
COFA Alliance National Network (CANN)|Marion CHD|1|1|JULY|2021
COFA Alliance National Network (CANN)|Union CHD|1|4|OCTOBER|2020
COFA Alliance National Network (CANN)|Union CHD|3|48|NOVEMBER|2020
COFA Alliance National Network (CANN)|Union CHD|1|1|JANUARY|2021
COFA Alliance National Network (CANN)|Union CHD|5|15|FEBRUARY|2021
Centro Latino Americano|Lane CHD|1|3|AUGUST|2020
Centro Latino Americano|Lane CHD|2|13|SEPTEMBER|2020
Centro Latino Americano|Lane CHD|2|8|OCTOBER|2020
Centro Latino Americano|Lane CHD|2|26|NOVEMBER|2020
Centro Latino Americano|Lane CHD|2|3|DECEMBER|2020
Centro Latino Americano|Lane CHD|1|3|JANUARY|2021
Centro Latino Americano|Lane CHD|1|1|MARCH|2021
Columbia Gorge Health Council|Hood River CHD|1|8|JANUARY|2021
Columbia Gorge Health Council|Hood River CHD|1|7|FEBRUARY|2021
Cornerstone Associates, Inc|Benton CHD|1|1|MARCH|2021
Cornerstone Associates, Inc|Benton CHD|1|2|JUNE|2021
Cornerstone Associates, Inc|Benton CHD|2|2|JULY|2021
Cornerstone Associates, Inc|Linn CHD|2|14|FEBRUARY|2021
Cornerstone Associates, Inc|Linn CHD|2|25|MARCH|2021
Cornerstone Associates, Inc|Linn CHD|2|11|APRIL|2021
;;;;
NOTE: 32 records were read from the infile _CODE_.
      The minimum record length was 3.
      The maximum record length was 66.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


NOTE: Fileref _CODE_ has been deassigned.

NOTE: Deleting WORK._DS2POST_ (memtype=DATA).
NOTE: PROCEDURE DELETE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

I ran some code to create a transposed dataset like this (that I saw in a Mike Zdeb article):

      CBO         Jurisdiction      _NAME_     June2020 .............. June2021

                                               Tracers

                                               Contacts

The code follows

proc sort Data = SASCDC_2.Arias_CBOs_with_Contacts_3;
by CBO;
run;

proc transpose data = SASCDC_2.Arias_CBOs_with_Contacts_3 out = SASCDC_2.Arias_Transpose_CBOs;                     
by CBO;
Var Tracers Contacts;
id Month Year;
run;

The problem is shown in the log.

 proc transpose data = SASCDC_2.Arias_CBOs_with_Contacts_3 out = SASCDC_2.Arias_Transpose_CBOs;
774  by CBO;
775  Var Tracers Contacts;
776  id Month Year;
777  run;

ERROR: The ID value "MARCH2021" occurs twice in the same BY group.
ERROR: The ID value "JULY2021" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      CBO=Cornerstone Associates, Inc
ERROR: The ID value "JANUARY2021" occurs twice in the same BY group.
ERROR: The ID value "DECEMBER2020" occurs twice in the same BY group.
ERROR: The ID value "JANUARY2021" occurs twice in the same BY group.
ERROR: The ID value "FEBRUARY2021" occurs twice in the same BY group.
ERROR: The ID value "APRIL2021" occurs twice in the same BY group.
ERROR: The ID value "JULY2021" occurs twice in the same BY group.
ERROR: The ID value "AUGUST2021" occurs twice in the same BY group.
ERROR: The ID value "OCTOBER2020" occurs twice in the same BY group.
ERROR: The ID value "NOVEMBER2020" occurs twice in the same BY group.
ERROR: The ID value "JANUARY2021" occurs twice in the same BY group.
ERROR: The ID value "FEBRUARY2021" occurs twice in the same BY group.
ERROR: The ID value "MARCH2021" occurs twice in the same BY group.
ERROR: The ID value "APRIL2021" occurs twice in the same BY group.
ERROR: The ID value "AUGUST2021" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      CBO=Eastern Oregon Center For Independent Living
ERROR: The ID value "DECEMBER2020" occurs twice in the same BY group.
ERROR: The ID value "JANUARY2021" occurs twice in the same BY group.
ERROR: The ID value "DECEMBER2020" occurs twice in the same BY group.
ERROR: The ID value "JANUARY2021" occurs twice in the same BY group.
ERROR: The ID value "FEBRUARY2021" occurs twice in the same BY group.
ERROR: The ID value "MARCH2021" occurs twice in the same BY group.
ERROR: The ID value "APRIL2021" occurs twice in the same BY group.
ERROR: The ID value "MAY2021" occurs twice in the same BY group.
ERROR: The ID value "AUGUST2021" occurs twice in the same BY group.
ERROR: The ID value "NOVEMBER2020" occurs twice in the same BY group.
ERROR: The ID value "JANUARY2021" occurs twice in the same BY group.
ERROR: The ID value "FEBRUARY2021" occurs twice in the same BY group.
ERROR: The ID value "MARCH2021" occurs twice in the same BY group.
ERROR: The ID value "APRIL2021" occurs twice in the same BY group.
ERROR: The ID value "JULY2021" occurs twice in the same BY group.
ERROR: The ID value "AUGUST2021" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      CBO=Euvalcree
ERROR: The ID value "JANUARY2021" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      CBO=La Clinica
ERROR: The ID value "NOVEMBER2020" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      CBO=Latino Community Association
ERROR: The ID value "JUNE2020" occurs twice in the same BY group.
ERROR: The ID value "JULY2020" occurs twice in the same BY group.
ERROR: The ID value "NOVEMBER2020" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      CBO=Northwest Family Services
ERROR: The ID value "AUGUST2020" occurs twice in the same BY group.
ERROR: The ID value "SEPTEMBER2020" occurs twice in the same BY group.
ERROR: The ID value "OCTOBER2020" occurs twice in the same BY group.
ERROR: The ID value "NOVEMBER2020" occurs twice in the same BY group.
ERROR: The ID value "JULY2020" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      CBO=Olalla Center
WARNING: 7 BY groups omitted due to earlier errors.
NOTE: There were 152 observations read from the data set SASCDC_2.ARIAS_CBOS_WITH_CONTACTS_3.
NOTE: The data set SASCDC_2.ARIAS_TRANSPOSE_CBOS has 16 observations and 18 variables.
NOTE: Compressing data set SASCDC_2.ARIAS_TRANSPOSE_CBOS increased size by 100.00 percent.
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

And also the monthyear  in the header row do not following chronologically.  For instance,  it can be  August2020    May2021    etc

Your help is appreciated, thank you.

wklierman

[wlierman]

should be August2021 May2020 February2021 etc

[Kurt_Bremser · Accepted solution]

First of all, by using month names, you will never get a chronological order, because August comes before any month not beginning with A (lexically). Convert your month/year first to something that sorts, like 2021-08 for August 2021. As it is ALWAYS better to store dates or date-related values as SAS dates, and assign appropriate formats (YYMMD7. will display the date 2021-08-01 as 2021-08, and TRANSPOSE will use the formatted value).

And next, add jurisdiction as a BY variable to avoid the duplicates.