topic Re: Forcing character length to be equal for characters, numbers, spaces and dots in SAS Procedures

Forcing character length to be equal for characters, numbers, spaces and dots

MichaelJustesen — Wed, 14 Oct 2015 10:00:37 GMT

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!

Re: Forcing character length to be equal for characters, numbers, spaces and dots

AskoLötjönen — Wed, 14 Oct 2015 10:19:10 GMT

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;

Re: Forcing character length to be equal for characters, numbers, spaces and dots

MichaelJustesen — Wed, 14 Oct 2015 10:39:14 GMT

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?

Re: Forcing character length to be equal for characters, numbers, spaces and dots

Patrick — Wed, 14 Oct 2015 11:03:05 GMT

If it's a .csv then there is no reason to use a fixed width output format.

Just use Proc Export

https://support.sas.com/documentation/cdl/en/proc/67916/HTML/default/viewer.htm#n0ku4pxzx3d2len10ozjgyjbrpl9.htm

Re: Forcing character length to be equal for characters, numbers, spaces and dots

MikeZdeb — Thu, 15 Oct 2015 12:47:55 GMT

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