topic Re: parsing values seperated by semi-colon in SAS Procedures

parsing values seperated by semi-colon

nickb — Wed, 08 Jun 2011 19:54:17 GMT

I'm working on a report that has copier/printing data and parsing the total pages printed is looking a little tricky. I have used PROC SQL to get the data from data source but now I'm working on a data step to get pages printed.

Most of the values are stored like:14 x Lgl/D and the total printed would be 14.

The other potential combinations: 14 x Lgl/D; 3 x Ltr; 86 x Ltr/D, separated by semi-colons. So for this transaction the total printed pages would 103 (14+3+86).

So I would need a data step that would handle all combinations and sum the total.

Re: parsing values seperated by semi-colon

sbb — Wed, 08 Jun 2011 21:14:00 GMT

You can use a SAS variable in your DATA step along with SCAN to capture each semi-colon delimited, and then use another assignment statement (like more than one for simplicity in coding) to parse each of the sub-records within a "transaction" -- then output one accumulated observation (or otherwise use PROC SUMMARY).

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

scan function parse delimited records site:sas.com

scan function site:sas.com

Re: parsing values seperated by semi-colon

Ksharp — Thu, 09 Jun 2011 02:32:15 GMT

You can use compress() to get these digits.

Ksharp

Re: parsing values seperated by semi-colon

nickb — Thu, 09 Jun 2011 14:31:56 GMT

compress out the semi colon?

Re: parsing values seperated by semi-colon

Peter_C — Thu, 09 Jun 2011 16:20:38 GMT

if you use an input statement to read these columns, just add ';' to the delimiters defined on the infile statement. The default delimiter is a blank, so you would be adding the option to the infile statement like
input 'the file you read' dlm=' ;' other options like lrecl=1000 and truncover ;

proc import offers less flexibility (I think)

peterC

Re: parsing values seperated by semi-colon

nickb — Thu, 09 Jun 2011 16:48:59 GMT

The data is in a temporary dataset, not a file.

Re: parsing values seperated by semi-colon

Peter_C — Thu, 09 Jun 2011 17:21:53 GMT

Sometimes it is worth loading a string into _INFILE_ just to get the sophisticated parsing available with INPUT

Re: parsing values seperated by semi-colon

nickb — Thu, 09 Jun 2011 20:11:34 GMT

I don't see example online on how to do this?

I have try this approach:

data test;
set work.adjunct_copy;
copies=substr(copy_pages,1, index(copy_pages,'x')-1); >>only 1 value is stored in the row
IF findc(Copy_Pages, ';')>1 then;
do while (findc(copy_pages, ';')=0); >>> loop for more than one
copies=substr(copy_pages,1, index(copy_pages,'x')-1);
end;
run;

Re: parsing values seperated by semi-colon

sbb — Thu, 09 Jun 2011 20:40:25 GMT

Here is one sample approach using a DO/END to parse through each logical within a physical record:

10 data _null_;
11 infile "c:\temp\parse_data_sas.txt";
12 input ;
13 length tempvar $100;
14 do i=1 to countw(_infile_,';');
15 tempvar = scan(_infile_,i,';');
16 putlog _all_;
17 * add code here to parse each sub-field. ;
18 end;
19 run;

NOTE: The infile "c:\temp\parse_data_sas.txt" is:
Filename=c:\temp\parse_data_sas.txt,
RECFM=V,LRECL=256,File Size (bytes)=43,
Last Modified=09Jun2011:16:35:41,
Create Time=09Jun2011:16:35:41

tempvar=14 x Lgl/D i=1 _ERROR_=0 _INFILE_=14 x Lgl/D; 3 x Ltr; 86 x Ltr/D _N_=1
tempvar=3 x Ltr i=2 _ERROR_=0 _INFILE_=14 x Lgl/D; 3 x Ltr; 86 x Ltr/D _N_=1
tempvar=86 x Ltr/D i=3 _ERROR_=0 _INFILE_=14 x Lgl/D; 3 x Ltr; 86 x Ltr/D _N_=1
tempvar=18 x Lgl/D i=1 _ERROR_=0 _INFILE_=18 x Lgl/D _N_=2
NOTE: 2 records were read from the infile "c:\temp\parse_data_sas.txt".
The minimum record length was 10.
The maximum record length was 31.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds

Scott Barry
SBBWorks, Inc.