I have a delimited file like this:
abc|def|ghi
klm|nop|qrs
and I want the output to appear like this:
abc
def
ghi
klm
nop
qrs
Please help.
Thank you.
Couldn't you use something like:
data want;
infile "/folders/myfolders/long.txt" lrecl=200000 delimiter='|';
length var $10;
input var @@;
run;
Art, CEO, AnalystFinder.com
Do like this
data have;
input string $50.;
datalines;
abc|def|ghi
klm|nop|qrs
;
data want(keep=word);
set have;
do i=1 to countw(string, '|');
word=scan(string, i, '|');
output;
end;
run;
Thanks for your quick response Draycut! And the problem for me here is I have the data in a CSV file which is inconsistent. The file doesn't have a header. And the no. of delimited values are not fixed within each row. Moreover, length of each row exceeds the allowed limit. So I can't have this in a separate datastep. I should somehow finish this while reading it in the first step.
very similar to @PeterClemmensen solution
data want(drop = i);
length col1 $ 3;
input @ ;
do i = 1 to countw(_infile_,'|');
col1 = scan(_infile_,i,'|');
output;
end;
datalines;
abc|def|ghi
klm|nop|qrs
;
@PeterClemmensen and @kiranv_ thanks a lot for your quick responses. I really appreciate that. The no. of characters in each of those delimited rows is as high as 100,000. So I am not able to hold that whole row as one record and my data is truncating. Could you please guide me on that please.. For example, I have abc|def|ghi|....| upto 40,000 characters.
just wondering to know whether you really need to read that long text. dos it make sense for your business.
@kiranv_ It's all delimited values and is very wide. And the width of the rows are not consistent throughout. Yes, it is important for me to read the data and load it..
@Cynthia_sas I have set the LRECL to 32760 with the infile option. I have rows longer than that 😞
@AshleyBright wrote:
@Cynthia_sas I have set the LRECL to 32760 with the infile option. I have rows longer than that 😞
This older post seems to have an solution: https://communities.sas.com/t5/SAS-Procedures/Losing-characters-during-reading-data-from-file/m-p/99...
Couldn't you use something like:
data want;
infile "/folders/myfolders/long.txt" lrecl=200000 delimiter='|';
length var $10;
input var @@;
run;
Art, CEO, AnalystFinder.com
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.