topic Re: Datalines Input in SAS Programming

Datalines Input

buechler66 — Wed, 15 May 2019 13:03:11 GMT

Hi. I'm trying to read in the following 3 lines into a single variable named 'SELECT'. So as each line is read in from the DATALINES statement I'm trying to append the lines together. But I cannot seem to get this to work. This is code I've been playing with, but it's not working. I'd love some help if you have time. Thanks.

DATA SELECT;
INFILE datalines TRUNCOVER;
INPUT SELECT $;
DATALINES;
SELECT DISTINCT
NAME
,ADDRESS
;
RETAIN SELECT;
LENGTH SELECT $32767;
CALL MISSING(SELECT);
SELECT=CATX(' ',SELECT,_INFILE_);
RUN;

The desired output would be:

SELECT

SELECT DISTINCT NAME ,ADDRESS

Re: Datalines Input

Tom — Wed, 15 May 2019 13:19:19 GMT

Why do you have lines of code after your data step? Where you trying to run do this in two data steps? If so you need a new data statement.

Don't overwrite your value. You can use the INPUT statement without any variables to give your program access to the _INFILE_ variable. Normally you would use the END= option on the INFILE statement to indicate a variable that you could test so you could output only at the end of the input. But that does not work with in-line data. So instead you can use the EOF= option which is used to point to label location in your code that should run when the end of the input data is reached.

DATA SELECT;
  INFILE datalines eof=write;
  length select $32767 ;
  retain select;
  input;
  select=catx(' ',select,_infile_);
return;
write: output;
DATALINES;
SELECT DISTINCT 
NAME
,ADDRESS
;

Re: Datalines Input

HenrikDorf — Wed, 15 May 2019 13:27:53 GMT

AArgh - 7 minutes to late .. 🙂

I have changed a few things - but this seems to work ..

Basically the "Input SELECT $;" statement would replace the accumulated contents with the contents of _INFILE_ , so i have removed that part of the code.

Also the output has to be controlled for only 1 observation in the output table.

Henrik

DATA SELECT;
INFILE datalines TRUNCOVER EOF=EOF;
LENGTH SELECT $32767;
INPUT ;
RETAIN SELECT;
SELECT=CATX(' ',SELECT,_INFILE_);
put select= ; * demo / debugging only ;
RETURN;
EOF: OUTPUT;
RETURN;
DATALINES;
SELECT DISTINCT
NAME
,ADDRESS
RUN;

Re: Datalines Input

buechler66 — Wed, 15 May 2019 14:45:57 GMT

Wow. Thank you so much.

That worked, but perhaps I should've provided my entire Datalines for input. Ultimately I'm hoping to read-in an entire Proc SQL query. But it appears that the semicolons in the query are going to mess up the Datalines statement. For example if you look at the first DATALINE that is input - it ends with a semicolon and I think that breaks the program. Is there any work-around for this?

DATA SELECT;
  INFILE datalines eof=write;
  length select $32767 ;  retain select;
  input;
  select=catx(' ',select,_infile_);
return;
write: output;
DATALINES;
PROC SQL;
SELECT DISTINCT 
		CLM.BENE_SK
		,1 as card
FROM	OPI_AAL.CLM AS CLM
INNER JOIN NFPP_TRD.&SYSUSERID._HHASWITCH_CLMS AS HHACLMS
	ON CLM.BENE_SK = HHACLMS.BENE_SK
WHERE (CLM.CLM_FROM_DT BETWEEN &bgn_dt AND &end_dt) 
	AND (CLM.CLM_PRNCPL_DGNS_CD LIKE 'I50%' or CLM.CLM_DGNS_1_CD LIKE 'I50%' or CLM.CLM_DGNS_2_CD LIKE 'I50%' 
     			or CLM.CLM_DGNS_3_CD LIKE 'I50%' or CLM.CLM_DGNS_4_CD LIKE 'I50%' or CLM.CLM_DGNS_5_CD LIKE 'I50%' 
				or CLM.CLM_DGNS_6_CD LIKE 'I50%' or CLM.CLM_DGNS_7_CD LIKE 'I50%' or CLM.CLM_DGNS_8_CD LIKE 'I50%')
	AND CLM.CLM_FINL_ACTN_IND = 'Y'
	AND CLM.CLM_TYPE_CD BETWEEN 2000 AND 2999
order by BENE_SK
;QUIT;

;

Re: Datalines Input

ballardw — Wed, 15 May 2019 14:48:39 GMT

Is the actual data you are going to read always going to read exactly 3 lines of data?

The read the values in one pass:

DATA SELECT;
   INFILE datalines TRUNCOVER;
   informat v1 v2 v3 $25.;
   INPUT v1 1-25
    / v2 1-25
    / v3 1-25
   ;
   length select $ 75;
   select= catx(' ',v1,v2,v3);
   /* drop v1 v2 v3;*/
DATALINES;
SELECT DISTINCT 
NAME
,ADDRESS
;

I explicitly read columns 1 to 25 in the example because things like Name and Address often have spaces and such so that your list style input would only read the first word. Adjust lengths as need. The / in the input says in effect "go to the next line and read"

I would suggest examining your data and determining if a length of $32767 is actually needed though.

Re: Datalines Input

buechler66 — Wed, 15 May 2019 14:52:06 GMT

No, the number of lines will vary. And if you read my latest reply I hit a new wrinkle that leaves me wondering if I can do this task at all using DATALINES.

Re: Datalines Input

Tom — Wed, 15 May 2019 14:53:26 GMT

If the in-line data contains semi-colons you can handle that by telling SAS to look for four semi-colons to mark the end of the in-line data.

datalines4;
blah blah; blah blah;
;;;;

Or better still just store the code to be read in a separate file instead of using in-line data.

Re: Datalines Input

buechler66 — Wed, 15 May 2019 15:43:51 GMT

Thanks so much for your help. I really appreciate you taking the time to help.

Re: Datalines Input

ballardw — Wed, 15 May 2019 16:17:55 GMT

@buechler66 wrote:
No, the number of lines will vary. And if you read my latest reply I hit a new wrinkle that leaves me wondering if I can do this task at all using DATALINES.

Crossed time. Your post of your actual problem was not there when I posted by my response.

Why datalines at all? Read a text file.

And from the character count of some of the code I see on this forum I'm not sure I would expect every single Proc Sql call to fit into a single variable to begin with.