BookmarkSubscribeRSS Feed
MichaelJustesen
Calcite | Level 5

 

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
4 REPLIES 4
AskoLötjönen
Quartz | Level 8

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;

MichaelJustesen
Calcite | Level 5

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?

MikeZdeb
Rhodochrosite | Level 12

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

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1239 views
  • 0 likes
  • 4 in conversation