topic SAS Transpose: Variables ordering in SAS Procedures

SAS Transpose: Variables ordering

1239 — Wed, 19 Oct 2011 07:28:24 GMT

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

SAS Transpose: Variables ordering

Ksharp — Wed, 19 Oct 2011 07:41:03 GMT

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

SAS Transpose: Variables ordering

1239 — Wed, 19 Oct 2011 07:55:18 GMT

Hi,

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

SAS Transpose: Variables ordering

Ksharp — Wed, 19 Oct 2011 08:45:45 GMT

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

Re: SAS Transpose: Variables ordering

data_null__ — Wed, 19 Oct 2011 11:47:21 GMT

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;

SAS Transpose: Variables ordering

1239 — Thu, 20 Oct 2011 10:04:59 GMT

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;

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;

Re: SAS Transpose: Variables ordering

data_null__ — Thu, 20 Oct 2011 11:21:25 GMT

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;

SAS Transpose: Variables ordering

1239 — Wed, 02 Nov 2011 11:03:36 GMT

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;

SAS Transpose: Variables ordering

1239 — Sat, 05 Nov 2011 07:23:11 GMT

Hi,

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

Re: SAS Transpose: Variables ordering

1239 — Sat, 05 Nov 2011 13:07:49 GMT

Hi All,

I have worked around my own requirement and I got the solution myself

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;

Re: SAS Transpose: Variables ordering

data_null__ — Sat, 05 Nov 2011 18:26:56 GMT

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.

Re: SAS Transpose: Variables ordering

Ksharp — Mon, 07 Nov 2011 07:04:11 GMT

Dictionary tables will be more simple.

Ksharp

Re: SAS Transpose: Variables ordering

1239 — Mon, 07 Nov 2011 09:12:41 GMT

Hi Ksharp,

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

Re: SAS Transpose: Variables ordering

Ksharp — Tue, 08 Nov 2011 02:32:31 GMT

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

SAS Transpose: Variables ordering

1239 — Mon, 14 Nov 2011 06:48:10 GMT

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

SAS Transpose: Variables ordering

Ksharp — Mon, 14 Nov 2011 07:25:59 GMT

You are welcome.