Hi,
I am trying to replicate the code(data step) generated by proc export. I cant use proc export thats why I am using the datastep.
My code is below:
****creating test dataset;
data tfl;
format a best4. b $3.;
a=1;b="abc";output;
a=2;b="bc";output;a=3;
b="acm";output;
run;
***generating proc contents to create the attributes macro variables;
proc contents data = tfl out =tfl_c;run;
proc sort data = tfl_c;by varnum;run;
**creating variables in dataset for put and format statement;
data tfl_c;
set tfl_c;
length fmt_str put_str $100;
fmt_str = strip(name) || " " ||strip(format) ||strip(formatl) || ".";
if type = 1 then put_str = "put "||strip(name) || " " || "@;";
else put_str = "put "||strip(name) || " " ||"$"||" "||"@;";
run;
** creating macro variables;
proc sql noprint;
select strip(fmt_str),strip(put_str), quote(strip(name))
into :fm_str_t separated by " ",:pt_str_t separated by " ",:tb_pt_t separated by " '09'x "
from tfl_c;
quit;
************Exporting title dataset to tfl txt file*******************;
data _null_;
file 'C:\temp\tfl.txt' delimiter='09'x
DROPOVER lrecl=32767;
if _n_ = 1 then do;
put &tb_pt_t. ;
end;
set TFL end=EFIEOD;
format &fm_str_t. ;
do;
EFIOUT + 1;
&pt_str_t.;;
end;
run;
Now , the output file generated is a tab delimited txt file, with all the data written in just one line. so The out put file is like ;
a | b | ||||
1 | abc | 2 | bc | 3 | acm |
But I want diiferent observations in dataset in different line,(just liek proc export does). If I dont use the macro variables and instead use explicit put statements, then it works fine.
Can anyone please help me on this. Thankyou!!
I don't have time right now to test your code, but adding one more put statement will probably achieve what you want. I put the extra statement in Caps.
data _null_;
file 'C:\temp\tfl.txt' delimiter='09'x
DROPOVER lrecl=32767;
if _n_ = 1 then do;
put &tb_pt_t. ;
end;
set TFL end=EFIEOD;
format &fm_str_t. ;
do;
EFIOUT + 1;
&pt_str_t.;;
end;
PUT;
run;
I don't have time right now to test your code, but adding one more put statement will probably achieve what you want. I put the extra statement in Caps.
data _null_;
file 'C:\temp\tfl.txt' delimiter='09'x
DROPOVER lrecl=32767;
if _n_ = 1 then do;
put &tb_pt_t. ;
end;
set TFL end=EFIEOD;
format &fm_str_t. ;
do;
EFIOUT + 1;
&pt_str_t.;;
end;
PUT;
run;
wow .. that worked!!
Thanks Arthur. Can u tell me the reason too why this was happening?
All of your put statements ended with an @ which told SAS not to issue a line feed. A put statement, by itself at the end, simply instructed SAS to output CarriageReturn/LineFeed characters.
Hi,
Not knowing your context, I know that it is too soon to say that your code is overly complicated to export a file.
However, I just can't help to deliver my 2 cents:
data tfl;
format a best4. b $3.;
a=1;b="abc";output;
a=2;b="bc";output;a=3;
b="acm";output;
run;
proc sql noprint;
select quote(trim(name)) into :varname separated by " '09'x " from dictionary.columns where LIBNAME='WORK' AND MEMNAME='TFL';
select trim(name) into :var separated by " '09'x " from dictionary.columns where LIBNAME='WORK' AND MEMNAME='TFL';
QUIT;
data _null_;
file 'C:\temp\tfl.txt'
DROPOVER lrecl=32767;
if _n_=1 then put &varname;
set tfl;
put &var.;
run;
Please check and let us know whether the above can meet your need.
Haikuo
Hi Haikou,
Actually I want to keep the code similar to the one generated by using proc export. Thanks!!
Hi,
I am getting another problem in this piece of code. This is actually a problem in Proc export too:
I am trying to the code mentioned in the first message of this discussion(i have changed the values of variable a here) ie
data tfl;
format a best4. b $3.;
a=1.10;b="abc";output;
a=2.20;b="bc";output;
a=3;b="acm";output;
run;
Now when I use proc export here(or the equivalent data step code), it eliminated the zeroes after the decimal place in my tab delimited file ie the value of a will become 1.1(instead of 1.10). Similarly 2.2(in place of 2.20). How can I stop this elimination of zeroes after decimal place from happening?
Thankyou!!
Try to use 'z5.2' instead of 'best4.'
Haikuo
actually these formats are picked from proc contents. I can hardcode any format.
I checked again. The promblem is with PROC IMPORT.
I have an excel , with values like 2.10,1.10. And when I am importing it to SAS dataset. they are imported as 2.1 and 1.1.
How can i stop this behaviour of Proc Import?
I would use something like 5.2 rather than z5.2 unless you want leading zeros as well. You have to adjust your code a bit to account for the decimals. e.g.:
data tfl;
format a 5.2 b $3.;
a=1.10;b="abc";output;
a=2.20;b="bc";output;
a=3;b="acm";output;
run;
***generating proc contents to create the attributes macro variables;
proc contents data = tfl out =tfl_c;run;
proc sort data = tfl_c;by varnum;run;
**creating variables in dataset for put and format statement;
data tfl_c;
set tfl_c;
length fmt_str put_str $100;
fmt_str = strip(name) || " " ||strip(format) ||strip(formatl) || "."||strip(formatd);
if type = 1 then put_str = "put "||strip(name) || " " || "@;";
else put_str = "put "||strip(name) || " " ||"$"||" "||"@;";
run;
** creating macro variables;
proc sql noprint;
select strip(fmt_str),strip(put_str), quote(strip(name))
into :fm_str_t separated by " ",:pt_str_t separated by " ",:tb_pt_t separated by " '09'x "
from tfl_c;
quit;
data _null_;
file 'C:\temp\\tfl.txt' delimiter='09'x
DROPOVER lrecl=32767;
if _n_ = 1 then do;
put &tb_pt_t. ;
end;
set TFL end=EFIEOD;
format &fm_str_t. ;
do;
EFIOUT + 1;
&pt_str_t.;;
end;
PUT;
run;
Hi Arthur, Thanks for the answer. But I guess the problem is I am importing an excel first to sas dataset and then I am exporting this sas dataset to tab delimited file.
So even when I am importing(using PROC IMPORT) it, the excel which has values like 5.10, 5.20,2.22 ; in SAS dataset they are converted to 5.1 and 5.2, 2.22 resp. But I dont want the zeroes to be eliminated.
Thanks!!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.