Hi,
I have following data set
Column - A
ac,dc,gh,th,kl
a,t,hj,tyu
xy,hgt,g125,ujy
How can I move it to
Column B
ac
dc
gh
th
kl
a
thj
tyu
.
.
.
I am using SAS DI, would appreciate if I could get a function for this.
There are several ways to do what you want.
1)
data have;
input a $20.;
datalines;
ac,dc,gh,th,kl
a,t,hj,tyu
xy,hgt,g125,ujy
;
run
data want;
set have;
length b $3;
i=1;
do until (b=' ');
b=scan(a,i,',');
if b not missing then output;
i+1;
end;
run;
2) Read data lines with delimiter=',' thus having 4 variables in step-1.
Then run proc transpose to convert wide form to long form, dropping _name_ column.
Hi, Shmuel
Thanks for the reply...I would clear out the question more...
I have a .txt file that I have to move into SQL database. This text file have five fields and I receive this file daily with around 5k records in it e.g.
Field1 Field2 Field3 Field4 Field5
id1 name1 age1 DOB1 chr1,chr2,chr3
id2 name2 age2 DOB2 chr4,chr5,chr6
I am using SAS DI for ETL,
The output should be as follows
Field1 Field2 Field3 Field4 Field5
id1 name1 age1 DOB1 chr1
id1 name1 age1 DOB1 chr2
id1 name1 age1 DOB1 chr3
id2 name2 age2 DOB2 chr4
id2 name2 age2 DOB2 chr5
id2 name2 age2 DOB2 chr6
So, please base the solution based on above scenario....would appreciate if you could help me based on SAS DI environment as we use transformations(eg. File Reader, Extract etc.) for ETL and we do not use BASE SAS.
I saw there is a TRANSPOSE transformation.. How do I use it...it is very confusing....an you guys help me with this?
Doing this in SQL is a big pain. Doing it in DI might be an even bigger pain.
Why not try it as multiple pulls, one for each possible entry.
So to get the first entry
select Field1
, Field2
, Field3
, Field4
, scan(Field5,1,',','mq') as Field5
from have
where countw(field5,',','mq') >= 1
Then either run it again but change 1 to 2 to get the second entries. Repeat for as many possible values that could appear.
Either run them separatedly or UNION them into one big query.
For an approach that lets you run the SQL code in Tom's SQL, but without the need for a separate run for each entry, see my ancient paper
https://support.sas.com/resources/papers/proceedings/proceedings/sugi31/046-31.pdf
Basically, you would create a utility table with as many rows as you have potential entries (so 5 entries means you have rows in the new table with the values 1, 2, 3, 4, 5) and add that table to the join, using the number from the utility table in the scan function. You'll get a warning about a cartesian join. If your "have" table (or a view created from it) has a column containing the number of output records you want, you can join on that number and make the process potentially faster.
It's also with DIS not that hard. You just can't use an out-of-the-box transformation which does everything for you but you need to implement user written code.
The DIS way is to implement such user written code directly within the External File object as documented here:
You then can use this External File object as you normally would (as input for a file reader) and then load the extracted table into target via whatever DIS transform is appropriate (like the Table Loader).
Below SAS code demonstrating how you can read your data into the desired target structure - so code close to what you will need for your External File.
/* create the sample external file */
filename extfile temp;
data _null_;
file extfile;
put
'Field1|Field2|Field3|Field4|Field5'/
'id1|name1|age1|DOB1|chr1,chr2,chr3'/
'id2|name2|age2|DOB2|chr4,chr5,chr6'
;
run;
/* read the external file into the desired table structure
- this code bit is close to what you need to implement in the external file object
*/
data read(drop=_:);
infile extfile dlm='|' dsd truncover firstobs=2;
input (Field1 Field2 Field3 Field4) (:$10.) _Field5 :$20.;
length Field5 $10;
_stop=sum(1,countc(_Field5,','));
do _i=1 to _stop;
Field5=scan(_Field5,_i,',');
output;
end;
run;
/* print the created SAS table */
proc print data=read;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.