Help using Base SAS procedures

SAS Transpose: Variables ordering

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

SAS Transpose: Variables ordering

Hi,

I have below code for transpose and my requirment is to get the Test in the order but the below code is not ordering as desired requirement. Please help me in getting the desired output. Thanks in Advance.

I have attached the input data.txt for reference.

data test;

infile "C:\input data.txt"

delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;

         informat Country $7. ;

          informat Number BEST12. ;

         informat Test $8. ;

         informat Result $3. ;

         format Country $7. ;

         format Number BEST12. ;

         format Test $8. ;

         format Result $3. ;

                 input

                  Country $

                  Number

                  Test $

                  Result $

          ;

run;

data test1 ;

set test ;

length testnum 8;

testnum = compress(TRANSLATE(upcase(test),' ','ABCDEFGHIJKLMNOPQRSTUVWXYZ/- '));

run;

proc sort data = test1;

by number testnum;

run;

proc transpose data= test1 out= test2 (drop = _name_) LET;

by country number;

var result;

id test;

idlabel test;

run;

Current Output:

Country          Number          TEST2          TEST6          TEST10          TEST18          TEST24          TEST14

Desired Output:

Country          Number          TEST2          TEST6          TEST10          TEST14          TEST18          TEST24

Attachment

Accepted Solutions
Solution
‎11-07-2011 09:32 PM
Super User
Posts: 9,671

Re: SAS Transpose: Variables ordering

OK.

data test;                                                                                                                              
   infile cards delimiter = '|' MISSOVER DSD firstobs=2;                                                                                
   input Country :$7. Number  Test :$8. Result :$3.;                                                                                    
   cards;                                                                                                                               
Country|Number|Test|Result|                                                                                                             
CONT|1|TEST2|80|                                                                                                                        
CONT|1|TEST6|86|                                                                                                                        
RI|2|TEST2|58|                                                                                                                          
SI|3|TEST2|94|                                                                                                                          
SI|3|TEST6|99|                                                                                                                          
SI|3|TEST10|78|                                                                                                                         
SI|3|TEST18|88|                                                                                                                         
WI|4|TEST2|94|                                                                                                                          
XI|5|TEST2|99|                                                                                                                          
XI|5|TEST24||                                                                                                                           
ZI|6|TEST2|77|                                                                                                                          
ZI|6|TEST6|55|                                                                                                                          
ZI|6|TEST10|44|                                                                                                                         
ZI|6|TEST14|34|                                                                                                                         
ZI|6|TEST18|33|                                                                                                                         
ZI|6|TEST24||                                                                                                                           
ZZI|7|VISIT43|99|                                                                                                                       
ZZI|7|VISIT44|123|                                                                                                                      
ZZI|7|VISIT45|44|                                                                                                                       
ZZI|7|VISIT46|12|                                                                                                                       
;;;;                                                                                                                                    
   run;                                                                                                                                 


proc transpose data= test1 out= test2 (drop = _name_) LET;                                                                              
by country number;                                                                                                                      
var result;                                                                                                                             
id test;                                                                                                                             
run;    
proc sql ;
 select name into : name separated by ' ' from dictionary.columns
    where libname='WORK' and memname='TEST2' and lowcase(name) not in ('country' 'number')
     order by substr(name,1,anydigit(name)-1),input(substr(name,anydigit(name)),best8.);
quit;
%put _user_;
data want;
 retain country number &name;
 set test2;
run;



Ksharp

View solution in original post


All Replies
Super User
Posts: 9,671

SAS Transpose: Variables ordering

Hi. But I can't find your attachment .

Actually you can change the order of variables manually. Like:

data test2;

retain Country      Number  test2-test24;

set test2;

run;

Ksharp

Contributor
Posts: 55

SAS Transpose: Variables ordering

Hi,

I have attached the file and Test varaibles are dynamic. Please help me.

Super User
Posts: 9,671

SAS Transpose: Variables ordering

OK.How about:

NOTE: I have chang txt file input data.txt into input_data.txt

data test;
infile "C:\input_data.txt"
delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;
         informat Country $7. ;
          informat Number BEST12. ;
         informat Test $8. ;
         informat Result $3. ;
         format Country $7. ;
         format Number BEST12. ;
         format Test $8. ;
         format Result $3. ;
                 input
                  Country $
                  Number
                  Test $
                  Result $
          ;
run;


 

data test1 ;
set test ;
length testnum 8;
testnum = input(substr(test,anydigit(test)),best8.);
run;

proc sql noprint;
 create table  temp as
  select *
   from (select distinct country from test1),
        (select distinct number from test1),
        (select distinct testnum from test1) 
    order by country,number,testnum;
 select count(testnum) into : count
  from temp 
   group by country,number;

quit;
%put &count;

proc sort data = test1;
by country number testnum;
run;
data x;
 merge test1 temp;
 by country number testnum;
run;
 

 

proc transpose data=x out= test2 (drop = _name_) prefix=test LET;
by country number;
var result;
id testnum;
run;
data want;
 set test2;
 if cmiss(of test:) lt &count;
run;

Ksharp

Respected Advisor
Posts: 3,777

Re: SAS Transpose: Variables ordering

We've discussed this quite a bit here and on SAS-L.  There is an option in PROC SORT that suits the problem very well.

data test;

   infile cards delimiter = '|' MISSOVER DSD firstobs=2;

   input Country :$7. Number  Test :$8. Result :$3.;

   cards;

Country|Number|Test|Result|

CONT|1|TEST2|80|

CONT|1|TEST6|86|

RI|2|TEST2|58|

SI|3|TEST2|94|

SI|3|TEST6|99|

SI|3|TEST10|78|

SI|3|TEST18|88|

WI|4|TEST2|94|

XI|5|TEST2|99|

XI|5|TEST24||

ZI|6|TEST2|77|

ZI|6|TEST6|55|

ZI|6|TEST10|44|

ZI|6|TEST14|34|

ZI|6|TEST18|33|

ZI|6|TEST24||

;;;;

   run;

proc sort data=test(keep=test) out=testord SORTSEQ=LINGUISTIC(NUMERIC_COLLATION=on) nodupkey;

   by test;

   run;

data topose / view=topose;

   if 0 then set test;

   set testord test;

   run;

proc transpose out=_data_(drop=_: where=(not missing(country)));

   by country number;

   var result;

   id test;

   run;

proc print;

   run;

Contributor
Posts: 55

SAS Transpose: Variables ordering

Hi data_null,

I tried your code, its working in windows environment but its not working in Unix environment. In realtime I will be running in Unix environment, please find the below error and help me to solve the same.

proc sort data=test(keep=test) out=testord SORTSEQ=LINGUISTIC(NUMERIC_COLLATION=on) nodupkey;

                                                                  -

                                                                  22

                                                                  76

ERROR 22-322: Syntax error, expecting one of the following: ;, ASCII, BUFFNO, DANISH, DATA, DATECOPY, DETAILS, DIAG, DUPOUT,

              EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE, LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS,

              NODUPLICATE, NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, OSA, OUT, OVERWRITE,

              PAGESIZE, PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI,

              THREADS, WKNO, WORKNO. 

ERROR 76-322: Syntax error, statement will be ignored.

27      by test;

28      run;

Respected Advisor
Posts: 3,777

Re: SAS Transpose: Variables ordering

Check your UNIX version.  I tested with 9.1 and that SORT option is not available.  But it is easy enough to fix with a data step and a sort.  The bottom line is to get the values of TEST in the order you want.

data test;

   infile cards delimiter = '|' MISSOVER DSD firstobs=2;

   input Country :$7. Number  Test :$8. Result :$3.;

   cards;

Country|Number|Test|Result|

CONT|1|TEST2|80|

CONT|1|TEST6|86|

RI|2|TEST2|58|

SI|3|TEST2|94|

SI|3|TEST6|99|

SI|3|TEST10|78|

SI|3|TEST18|88|

WI|4|TEST2|94|

XI|5|TEST2|99|

XI|5|TEST24||

ZI|6|TEST2|77|

ZI|6|TEST6|55|

ZI|6|TEST10|44|

ZI|6|TEST14|34|

ZI|6|TEST18|33|

ZI|6|TEST24||

;;;;

   run;

proc sort data=test(keep=test) out=testord /*SORTSEQ=LINGUISTIC(NUMERIC_COLLATION=on)*/ nodupkey;

   by test;

   run;

data testord;

   set testord;

   order = input(compress(test,,'A'),8.);;

   run;

proc sort data=testord;

   by order;

   run;

proc print;

   run;

data topose / view=topose;

   if 0 then set test;

   set testord(keep=test) test;

   run;

proc transpose out=_data_(drop=_: where=(not missing(country)));

   by country number;

   var result;

   id test;

   run;

proc print;

   run;

Contributor
Posts: 55

SAS Transpose: Variables ordering

Hi,

The below code is working fine as per the requirement. The only thing is everytime hardcoding cannot be done.

How do I write the below code in Macro so that dynamically variables are selected? why because test2-test24 variable names will not remain for same in real time situation, it will be varying.

Example: test2-test24, T2-T24, VT2-VT24 so on...

data test2;

retain Country      Number  test2-test24;

set test2;

run;

Contributor
Posts: 55

SAS Transpose: Variables ordering

Hi,

Please anyone help. Its very urgent. Thanks in Advance.

Contributor
Posts: 55

Re: SAS Transpose: Variables ordering

Hi All,

I have worked around my own requirement and I got the solution myselfSmiley Happy

I request at least somebody let me know whether this code can be written in more efficient way? or this is correct?

data test;                                                                                                                             

   infile cards delimiter = '|' MISSOVER DSD firstobs=2;                                                                               

   input Country :$7. Number  Test :$8. Result :$3.;                                                                                   

   cards;                                                                                                                              

Country|Number|Test|Result|                                                                                                            

CONT|1|TEST2|80|                                                                                                                       

CONT|1|TEST6|86|                                                                                                                       

RI|2|TEST2|58|                                                                                                                         

SI|3|TEST2|94|                                                                                                                         

SI|3|TEST6|99|                                                                                                                         

SI|3|TEST10|78|                                                                                                                        

SI|3|TEST18|88|                                                                                                                        

WI|4|TEST2|94|                                                                                                                         

XI|5|TEST2|99|                                                                                                                         

XI|5|TEST24||                                                                                                                          

ZI|6|TEST2|77|                                                                                                                         

ZI|6|TEST6|55|                                                                                                                         

ZI|6|TEST10|44|                                                                                                                        

ZI|6|TEST14|34|                                                                                                                        

ZI|6|TEST18|33|                                                                                                                        

ZI|6|TEST24||                                                                                                                          

ZZI|7|VISIT43|99|                                                                                                                      

ZZI|7|VISIT44|123|                                                                                                                     

ZZI|7|VISIT45|44|                                                                                                                      

ZZI|7|VISIT46|12|                                                                                                                      

;;;;                                                                                                                                   

   run;                                                                                                                                

data test1 ;                                                                                                                           

set test ;                                                                                                                             

length testnum 8;                                                                                                                      

testnum = compress(TRANSLATE(upcase(test),' ','ABCDEFGHIJKLMNOPQRSTUVWXYZ/- '));                                                       

run;                                                                                                                                   

proc sort data = test1;                                                                                                                

by number testnum;                                                                                                                     

run;                                                                                                                                   

proc transpose data= test1 out= test2 (drop = _name_) LET;                                                                             

by country number;                                                                                                                     

var result;                                                                                                                            

id testnum;                                                                                                                            

idlabel test;                                                                                                                          

run;                                                                                                                                   

proc sql;                                                                                                                              

create table test_visit as                                                                                                             

select distinct test, testnum from test1 order by testnum;                                                                             

quit;                                                                                                                                  

proc sql;                                                                                                                              

create table visit as                                                                                                                  

select min(testnum), max(testnum) into :min, :max                                                                                      

from test_visit;                                                                                                                       

quit;                                                                                                                                  

proc sql noprint;                                                                                                                      

select min(testnum), max(testnum) into :min, :max                                                                                      

from test_visit;                                                                                                                       

quit;                                                                                                                                  

%put min &min;                                                                                                                         

%put max &max;                                                                                                                         

data min;                                                                                                                              

mina =  '_'||strip( put(&min., 8.)) ;                                                                                                  

maxa =  '_'||strip( put(&max., 8.));                                                                                                   

run;                                                                                                                                   

proc sql noprint;                                                                                                                      

select mina, maxa into :mina, :maxa                                                                                                    

from min;                                                                                                                              

quit;                                                                                                                                  

%put mina &mina;                                                                                                                       

%put maxa &maxa;                                                                                                                       

data test5;                                                                                                                            

retain Country Number &mina-&maxa;                                                                                                     

set test2;                                                                                                                             

run;

Respected Advisor
Posts: 3,777

Re: SAS Transpose: Variables ordering

I showed you a better way to do it in this message http://communities.sas.com/message/107707#107707 after you complained that my program that used a PROC SORT feature that does not exist in 9.1.3 did not work for you.

Super User
Posts: 9,671

Re: SAS Transpose: Variables ordering

Dictionary tables will be more simple.

Ksharp

Contributor
Posts: 55

Re: SAS Transpose: Variables ordering

Hi Ksharp,

Will you please help me to achieve through Dictionary tables? Thanks

Solution
‎11-07-2011 09:32 PM
Super User
Posts: 9,671

Re: SAS Transpose: Variables ordering

OK.

data test;                                                                                                                              
   infile cards delimiter = '|' MISSOVER DSD firstobs=2;                                                                                
   input Country :$7. Number  Test :$8. Result :$3.;                                                                                    
   cards;                                                                                                                               
Country|Number|Test|Result|                                                                                                             
CONT|1|TEST2|80|                                                                                                                        
CONT|1|TEST6|86|                                                                                                                        
RI|2|TEST2|58|                                                                                                                          
SI|3|TEST2|94|                                                                                                                          
SI|3|TEST6|99|                                                                                                                          
SI|3|TEST10|78|                                                                                                                         
SI|3|TEST18|88|                                                                                                                         
WI|4|TEST2|94|                                                                                                                          
XI|5|TEST2|99|                                                                                                                          
XI|5|TEST24||                                                                                                                           
ZI|6|TEST2|77|                                                                                                                          
ZI|6|TEST6|55|                                                                                                                          
ZI|6|TEST10|44|                                                                                                                         
ZI|6|TEST14|34|                                                                                                                         
ZI|6|TEST18|33|                                                                                                                         
ZI|6|TEST24||                                                                                                                           
ZZI|7|VISIT43|99|                                                                                                                       
ZZI|7|VISIT44|123|                                                                                                                      
ZZI|7|VISIT45|44|                                                                                                                       
ZZI|7|VISIT46|12|                                                                                                                       
;;;;                                                                                                                                    
   run;                                                                                                                                 


proc transpose data= test1 out= test2 (drop = _name_) LET;                                                                              
by country number;                                                                                                                      
var result;                                                                                                                             
id test;                                                                                                                             
run;    
proc sql ;
 select name into : name separated by ' ' from dictionary.columns
    where libname='WORK' and memname='TEST2' and lowcase(name) not in ('country' 'number')
     order by substr(name,1,anydigit(name)-1),input(substr(name,anydigit(name)),best8.);
quit;
%put _user_;
data want;
 retain country number &name;
 set test2;
run;



Ksharp

Contributor
Posts: 55

SAS Transpose: Variables ordering

Thanks a lot for your code KsharpSmiley Happy. Its working as per the requirement.

☑ This topic is SOLVED.

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

Discussion stats
  • 15 replies
  • 766 views
  • 0 likes
  • 3 in conversation