BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
1239
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

15 REPLIES 15
Ksharp
Super User

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

1239
Calcite | Level 5

Hi,

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

Ksharp
Super User

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

data_null__
Jade | Level 19

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;

1239
Calcite | Level 5

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;

data_null__
Jade | Level 19

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;

1239
Calcite | Level 5

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;

1239
Calcite | Level 5

Hi,

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

1239
Calcite | Level 5

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;

data_null__
Jade | Level 19

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.

Ksharp
Super User

Dictionary tables will be more simple.

Ksharp

1239
Calcite | Level 5

Hi Ksharp,

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

Ksharp
Super User

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

1239
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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