Help using Base SAS procedures

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

Reply
New Contributor
Posts: 2

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

 

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!

 


My output.pngOutput should look like.png
Contributor
Posts: 44

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

Posted in reply to MichaelJustesen

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;

New Contributor
Posts: 2

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

Posted in reply to AskoLötjönen

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?

Respected Advisor
Posts: 4,173

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

[ Edited ]
Posted in reply to MichaelJustesen

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#n0ku4pxzx3d2len10ozj...

 

Valued Guide
Posts: 765

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

Posted in reply to MichaelJustesen

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

Ask a Question
Discussion stats
  • 4 replies
  • 285 views
  • 0 likes
  • 4 in conversation