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:
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.);
/*REPLACES BLANKS WITHIN VARIABLES WITH & FOR CONCATE WITHOUT SPACES*/
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, ' ', '&');
/*CONCATENATES TO A SINGLE STRING*/
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
/*REPLACES & WITH BLANKS IN CONCATENATED STRING WITHOUT SPACES/DELIMITERS*/
Concatenated = tranwrd(Concatenated, '&', ' ');
Can anyone help me?
Why you are not writing directly to ascii file with defined columns:
filename out 'C:\test.txt';
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;
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;
ods csv close;
partial view of CLASS.CSV ...
If you just want a file with fixed with output for all variables with the numbers right justified ...
* Z format adds leading zeroes;
For example ...
* rounds height and weight, adds leading zeroes;
partial view of CLASS.TXT ...
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.