I have a table with multiple data rows for multiple variables. My issue is that some of the variables in some of the data rows are missing, and when I concate all the variables to one field, SAS assigns a different length to the missing values than to other values.
I need my export to have the same exact placement of each variable (whether or not it is blank or not) but I cannot achieve this as the data rows with missing values take up less space than the other data rows.
I have attached 2 pictures of what my output looks like, and what it should look like.
My code is:
/*LENGTH FORMATTING*/
DATA total_formated;
set CVR_no_incl;
Country1 = put(country,2.);
Rcd_type1 = put(Rcd_type, 1.);
char_dealer1 = put(left(char_dealer), 10.);
char_Inv_nbr1 = put(left(char_Inv_nbr), 7.);
char_Doc_type1 = put(right(char_Doc_type), 3.);
char_Doc_date1 = put(right(char_Doc_date), 8.);
char_Outstanding_amount1 = put(right(char_Outstanding_amount), 13.);
char_Credit_limit1 = put(right(char_Credit_limit), 13.);
run;
/*REPLACES BLANKS WITHIN VARIABLES WITH & FOR CONCATE WITHOUT SPACES*/
Data And_instead_of_blank;
set total_formated;
Country1 = tranwrd(Country1, ' ', '&');
Rcd_type1 = tranwrd(Rcd_type1, ' ', '&');
char_dealer1 = tranwrd(char_dealer1, ' ', '&');
char_Inv_nbr1 = tranwrd(char_Inv_nbr1, ' ', '&');
char_Doc_type1 = tranwrd(char_Doc_type1, ' ', '&');
char_Doc_date1 = tranwrd(char_Doc_date1, ' ', '&');
char_Outstanding_amount1 = tranwrd(char_Outstanding_amount1, ' ', '&');
char_Credit_limit1 = tranwrd(char_Credit_limit1, ' ', '&');
run;
Data Ready_for_concate;
set And_instead_of_blank;
length
Country1 $2
Rcd_type1 $1
char_dealer1 $10
char_Inv_nbr1 $7
char_Doc_type1 $3
char_Doc_date1 $8
char_Outstanding_amount1 $13
char_Credit_limit1 $13;
run;
/*CONCATENATES TO A SINGLE STRING*/
PROC SQL;
create table Concatenated as
select cat(Country1, Rcd_type1, char_dealer1, char_Inv_nbr1, char_Doc_type1, char_Doc_date1, char_Outstanding_amount1, char_Credit_limit1) as Concatenated
from Ready_for_concate;
quit;
/*REPLACES & WITH BLANKS IN CONCATENATED STRING WITHOUT SPACES/DELIMITERS*/
Data Ready_for_export;
set Concatenated;
Concatenated = tranwrd(Concatenated, '&', ' ');
run;
Can anyone help me?
Thank you!
Why you are not writing directly to ascii file with defined columns:
filename out 'C:\test.txt';
data have;
set have;
file out ;
put @1 Country @3 Rcd_type @4 char_dealer @14 char_Inv_nbr @21 char_Doc_type @24 char_Doc_date @31 char_Outstanding_amount @44 char_Credit_limit;
run;
Thank you for the suggestion!
I need to deliver the file in .csv format however.
Also, there cannot be spaces between the variables which the solution you suggested includes (this is why I change spaces into & characters before concating, then use the cat function for concating the variables, and then change the & characters back into spaces - this only leaves spaces where there are blank/missing characters from the values) - I can manage this by changing the starting position of the variables however so this is not a problem.
Can I use a combination of the put sentence you suggested with an export to csv?
If it's a .csv then there is no reason to use a fixed width output format.
Just use Proc Export
Hi. If you want a CSV file, you can just use PROC PRINT, for example ...
ods listing close;
ods results off;
ods csv file='z:\class.csv';
proc print data=sashelp.class noobs;
run;
ods csv close;
ods listing;
ods results;
partial view of CLASS.CSV ...
"Name","Sex","Age","Height","Weight"
"Alfred","M",14,69.0,112.5
"Alice","F",13,56.5,84.0
"Barbara","F",13,65.3,98.0
"Carol","F",14,62.8,102.5
"Henry","M",14,63.5,102.5
If you just want a file with fixed with output for all variables with the numbers right justified ...
* Z format adds leading zeroes;
data _null_;
file 'z:\fixed.txt';
set CVR_no_incl;
put
country z2.
Rcd_type z1.
char_dealer z10.
char_Inv_nbr z7.
char_Doc_type z3.
char_Doc_date z8.
char_Outstanding_amount z13.
char_Credit_limit z13.
;
run;
For example ...
* rounds height and weight, adds leading zeroes;
data _null_;
file 'z:\class.txt';
set sashelp.class;
put
age z5.
height z5.
weight z5.
;
run;
partial view of CLASS.TXT ...
000140006900113
000130005700084
000130006500098
000140006300103
000140006400103
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.