@Claudia_SAS wrote:
thank you for your reply .....yes...I calculated the length of the rpo column, and then the length of opt(i) always equals to 3, delimiter is always one space.
Not a trivial exercise but not extremely difficult. However a more important question may be what you will do with all of those variables when created. For many types of analysis a single variable with different values is sufficient.
So here are the steps involved to one approach:
1) get a list of ALL of the codes involved. If you have others appear in a later data step that will be a serious complication to your "in order". If you do not have such a list already and have to create one from your existing data the first step is go from one wide variable to a bunch of single values.
2) create a temporary array in a data step with the values
3) identify which order each value in the list has from the temporary array
4) assign the variable the value.
Here is one way. Note the example data is small because I am not going to TYPE from pictures a larger number of values.
data start;
infile datalines truncover;
input country $ opt $20.;
datalines;
US #02 #05 #38 Z82
US #02 #16 #33 ZY1
;
/* get each code value as a single record*/
data temp;
set start;
length o $ 3;
do i=1 to countw(opt);
o = scan(opt,i);
output;
end;
keep o;
run;
/* create ordered list of codes suitable for use in
temporary array definition
*/
Proc sql noprint;
select distinct quote(o) into: codelist separated by ','
from temp;
quit;
%let codecount=&sqlobs;
%put list of codes = &codelist.;
%put number of codes = &codecount.;
data want;
set start;
array opt_(&codecount.) $ 3 opt1 - opt&codecount. ;
array t(&codecount.) $ 3 _temporary_( &codelist);
do i= 1 to countw(opt);
opt_(whichc(scan(opt,i),of t(*))) = scan(opt,i);
end;
drop i;
run;
Some key elements. The temp data set gets all of the values as a single record per value.
The Proc sql select distinct will get the unique values, and by default in an order. If you expect the aphabetic Z82 to appear before #02 that will add some work. There are two macro variables created that you can examine in the log that will be used to write part of the code needed for the last step. The &sqlobs is an automatic SAS variable that returns the number of observations used in the immediately previous Proc SQL step. Assign the value to a known variable so you can retrieve the value later.
The macro variable codecount has how many codes are appear in the data. This is the issue if you run this later with other data that the value of OPTx may change because of the sorting if new code values are added.
In the last data step we use the the codecount to set the size of arrays and create the variable names in the array statement.
The marginally ugly code in the do loop involving WHICHC is a function that finds the position of a value in the list of values. In this case the list of values is the temporary array T and the "of t(*)" say to search all of the values in the array. It returns the number of the found element.
Another approach would be create numeric 0/1 coded variables assigning labels to the variables Opt1 to Optn of the value represented, with 1 indicating "found" and 0 "not found". Which coding makes more sense depends on how you intend to use the data.
