topic Re: sorting according to PROC FORMAT in SAS Programming

sorting according to PROC FORMAT

GreggB — Thu, 15 Jun 2017 19:50:52 GMT

I have the var Code1 that has the following values:

Code1 Frequency
*DD
*OHI
AU
EH
HH
ID
LD
MD
OH
SP

They are formatted like so:

proc format;
value $ iepfmt
EM='1. Intellectual Disabilities' TM='1. Intellectual Disabilities' HH='2. Hearing Impairments' SP='3. Speech Impairments' VH='4. Visual Impairments'
EH='5. Emotional Disturbance'
OH='6. Orthopedic Impairments' '*OHI'='7. Other Health Impairments' LD='8. Specific Learning Disabilities' MD='10. Multiple Disabilities' AU='11. Autism'
'*TBI'='12. Traumatic Brain Injury' '*DD'='13. Developmental Delay'
ID='1. Intellectual Disabilities' '*PMD'='1. Intellectual Disabilities'
;
run;

I want to sort the variable Code 1 according to the number in char string of the format so that '1. Intellectual Disabilities' appears first etc and '13. Developmental Delay' appears last in the printout.

Re: sorting according to PROC FORMAT

jdwaterman91 — Thu, 15 Jun 2017 21:02:05 GMT

Sorting according to a format can be a bit tricky.

Try assigning each code a num variable and then sort on the number variable like below:

/* Inputting the data */

Data Code;
Input Code1 $;
Datalines;
*DD
*OHI
AU
EH
HH
ID
LD
MD
OH
SP
VH
;
Run;

/* Creating "Type" and "Num" variables based on the desired formats above"

Data Calc;
Set Code;

If Code1 in ('EM' 'TM' 'ID' '*PMD') Then do;
Type = "Intellectual Disabilities";
Num = 1; End;
Else If Code1 = 'HH' Then do;
Type = "Hearing Impariments";
Num =2; End;
Else If Code1 = 'SP' Then do;
Type = "Speech Impairments";
Num = 3; End;
Else If Code1 = 'VH' Then do;
Type = "Visual Impairments";
Num = 4; End;
Else If Code1 = 'EH' Then do;
Type = "Emotional Disturbance";
Num = 5; End;
Else If Code1 = 'OH' Then do;
Type = "Orthopedic Impairments";
Num = 6; End;
Else If Code1 = '*OHI' Then do;
Type = "Other Health Impairments";
Num = 7; End;
Else If Code1 = 'LD' Then do;
Type = "Specific Learning Disabilities";
Num = 8; End;
Else If Code1 = 'MD' Then do;
Type = "Multiple Disabilities";
Num = 10; End;
Else If Code1 = 'AU' Then do;
Type = "Autism";
Num = 11; End;
Else If Code1 = '*TBI' Then do;
Type = "Traumatic Brain Injury";
Num = 12; End;
Else If Code1 = '*DD' Then do;
Type = "Developmental Delay";
Num = 13; End;
Run;

/* Sorting the data */

Proc Sort data = calc;
By Num;
Run;

Re: sorting according to PROC FORMAT

art297 — Thu, 15 Jun 2017 21:14:12 GMT

Would something like the following suffice?:

proc format;
value $iepfmt
EM='1. Intellectual Disabilities' TM='1. Intellectual Disabilities' HH='2. Hearing Impairments' SP='3. Speech Impairments' VH='4. Visual Impairments' 
EH='5. Emotional Disturbance'
OH='6. Orthopedic Impairments' '*OHI'='7. Other Health Impairments' LD='8. Specific Learning Disabilities' MD='10. Multiple Disabilities' AU='11. Autism'
'*TBI'='12. Traumatic Brain Injury' '*DD'='13. Developmental Delay' 
ID='1. Intellectual Disabilities' '*PMD'='1. Intellectual Disabilities'
;
run;
data have;
  input Code1 $;
  format Code1 $iepfmt.;
  _Code1=put(Code1,$iepfmt.);
  cards;
*DD 
*OHI
AU 
EH 
HH 
ID  
LD 
MD 
OH 
SP 
VH
;
 
proc sort data=have sortseq=linguistic (NUMERIC_COLLATION=ON) out=want (drop=_:);
  by _Code1;
run;

Art, CEO, AnalystFinder.com

Re: sorting according to PROC FORMAT

collinelliot — Thu, 15 Jun 2017 23:18:40 GMT

You could also sort using proc sql with an ORDER BY. If your formats were padded it would not require the extra manipulation, but it's doable (if inefficient, possibly) regardless.

proc format;
value $iepfmt
EM='1. Intellectual Disabilities' TM='1. Intellectual Disabilities' HH='2. Hearing Impairments' SP='3. Speech Impairments' VH='4. Visual Impairments' 
EH='5. Emotional Disturbance'
OH='6. Orthopedic Impairments' '*OHI'='7. Other Health Impairments' LD='8. Specific Learning Disabilities' MD='10. Multiple Disabilities' AU='11. Autism'
'*TBI'='12. Traumatic Brain Injury' '*DD'='13. Developmental Delay' 
ID='1. Intellectual Disabilities' '*PMD'='1. Intellectual Disabilities'
;
run;
data have;
  input Code1 $;
  format Code1 $iepfmt.;
  cards;
*DD 
*OHI
AU 
EH 
HH 
ID  
LD 
MD 
OH 
SP 
VH
;

proc sql;
    CREATE TABLE want AS
    SELECT * 
    FROM have
    ORDER BY input(scan(put(code1, $iepfmt.), 1, '.'), best.);
/*    ORDER BY put(code1, $iepfmt.); If formats are padded 01 02 03...*/
quit;

Re: sorting according to PROC FORMAT

GreggB — Mon, 19 Jun 2017 12:49:22 GMT

Nice! Thanks.