DATA Step, Macro, Functions and more

sorting according to PROC FORMAT

Accepted Solution Solved
Reply
Super Contributor
Posts: 279
Accepted Solution

sorting according to PROC FORMAT

I have the var Code1 that has the following values:

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

VH

 

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.

 

 

 

Accepted Solutions
Solution
‎06-19-2017 08:49 AM
PROC Star
Posts: 7,468

Re: sorting according to PROC FORMAT

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

 

View solution in original post


All Replies
Contributor
Posts: 22

Re: sorting according to PROC FORMAT

[ Edited ]

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;

Solution
‎06-19-2017 08:49 AM
PROC Star
Posts: 7,468

Re: sorting according to PROC FORMAT

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

 

Super Contributor
Posts: 279

Re: sorting according to PROC FORMAT

Nice! Thanks.

PROC Star
Posts: 307

Re: sorting according to PROC FORMAT

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 163 views
  • 2 likes
  • 4 in conversation