10-14-2015 06:00 AM
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?
10-14-2015 06:19 AM
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;
10-14-2015 06:39 AM
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?
10-14-2015 07:01 AM - edited 10-14-2015 07:03 AM
If it's a .csv then there is no reason to use a fixed width output format.
Just use Proc Export
10-15-2015 08:47 AM
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 ...