How to avoid Carriage Return while creating file in SAS?

Reply
Occasional Contributor
Posts: 11

How to avoid Carriage Return while creating file in SAS?

Hi,

I have this code in Proc SQL which converts top three Plans for Physicians into columns. For eg :

INPUT DATASET
Physician NumberNamePlan RankPlanSales
ADavid1a20
ADavid2b30
ADavid3c40
BJames1e25
BJames2d35
BJames3f45

OUTPUT DATASET
Physician NumberNameP1P2P3
ADavidabc
BJamesefd

For this, I am using the following code :

PROC SQL;

CREATE TABLE T1 AS

SELECT Physician Number, Name, Plan as P1, '' AS P2,'' AS P3 FROM INPUT DATASET WHERE Plan Rank =1

UNION

SELECT Physician Number, Name, '' as P1, Plan AS P2,'' AS P3 FROM INPUT DATASET WHERE Plan Rank =2

UNION

SELECT Physician Number, Name, '' as P1, '' AS P2,Plan AS P3 FROM INPUT DATASET WHERE Plan Rank =3;


CREATE TABLE T2 AS

SELECT Physician Number,Name, MAX(P1),MAX(P2),MAX(P3) FROM INPUT DATASET GROUP BY Physician Number,Name;

QUIT;


PROC export DATA= INTMD_PLAN_LEVEL_DATA2   

outfile= '~/OUTPUT.txt' DBMS  = TAB REPLACE;

RUN;       


The problem is when I open the file in notepad, I get Carriage Return after P1 ( i.e "Enter"- whitespace character).

Any idea why is this coming. Is this because I am inserting blank in columns ?

Occasional Contributor
Posts: 11

Re: How to avoid Carriage Return while creating file in SAS?

Just to correct here, the last part of the code is :

PROC export DATA= T2 

outfile= '~/OUTPUT.txt' DBMS  = TAB REPLACE;

RUN;      

Super User
Super User
Posts: 7,720

Re: How to avoid Carriage Return while creating file in SAS?

Sorry, I am not clear on what software you are working in here.  Your SQL code is invalid:

CREATE TABLE T1 AS

SELECT Physician Number, Name, Plan as P1, '' AS P2,'' AS P3 FROM INPUT DATASET WHERE Plan Rank =1

Valid SAS names do not contain spaces.

As for your export, I have run it on my SAS 9.3 install and it works fine.  Have you tried dbms=csv, if there is a return in the data field then its likely to either be in your data or you may have an escape character, or possibly an option has been set.  However from your code I cannot tell.  Post a datastep, with some test data which causes you the problem, and tell us what software you are using.

Occasional Contributor
Posts: 11

Re: How to avoid Carriage Return while creating file in SAS?

Hi,

Forget about the spaces as for now in SQL code. I have just created a shorter version of my actual code. I am trying the dbms = csv now. Let me see if the code runs. I am using SAS 9.2.

Super User
Super User
Posts: 6,845

Re: How to avoid Carriage Return while creating file in SAS?

To make your output dataset from your input data set you should use PROC TRANSPOSE and not PROC SQL.

data have ;

  INPUT id $ name $ Rank Plan $ Sales;

cards;

A David 1 a 20

A David 2 b 30

A David 3 c 40

B James 1 e 25

B James 2 d 35

B James 3 f 45

;;;;

proc transpose data=have out=want(drop=_name_) prefix=P;

  by id name ;

  id rank ;

  var plan ;

run;


Obs    id    name     P1    P2    P3

1     A     David    a     b     c

2     B     James    e     d     f

Occasional Contributor
Posts: 11

Re: How to avoid Carriage Return while creating file in SAS?

Hi Tom,

The above code would definitely work for the given dataset. But what if the data set contains Plan Rank from 1 to 10 and I need top 3 plans column wise. I guess for that we need to Proc SQL.

Correct if I am wrong. Thanks!

Super User
Super User
Posts: 6,845

Re: How to avoid Carriage Return while creating file in SAS?

If the RANK has already been applied then just filter the data on the way into PROC TRANSPOSE.

proc transpose data=have (where=(rank <=3)) out=want(drop=_name_) prefix=P;

  by id name ;

  id rank ;

  var plan ;

run;

If RANK has not been calculate, but the data is sorted already then drop the columns on the way out.

proc transpose data=have out=want(keep=id name p1-p3) prefix=P;

  by id name ;

  var plan ;

run;


Ask a Question
Discussion stats
  • 6 replies
  • 335 views
  • 0 likes
  • 3 in conversation