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
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
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
Hi,
I have attached the file and Test varaibles are dynamic. Please help me.
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
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;
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;
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;
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;
Hi,
Please anyone help. Its very urgent. Thanks in Advance.
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;
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.
Dictionary tables will be more simple.
Ksharp
Hi Ksharp,
Will you please help me to achieve through Dictionary tables? Thanks
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
Thanks a lot for your code Ksharp. Its working as per the requirement.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.