topic Re: How to Output to One Record After Assiging Values in SAS Programming

How to Output to One Record After Assiging Values

FollinLane — Thu, 14 Feb 2019 19:34:49 GMT

Hi,

I'm reading in a file using the code below. I'm new as using the scan function and would like to know how to output to the record after each iteration of through the code.

Thank you

data testdata2;
    infile mysource lrecl = 6807 truncover scanover;
        input
            @'"UserEmail"|"metadata_value":"' user_email $100.
			@'"ProductVersion"|"metadata_value":"' prod_version $20.
			@'"SourceEvent"|"metadata_value":"' source_event $30.
			@'"ProcessTotalWaitTime"|"metadata_value":"' PT_WaitTime $10.
			;

        user_email = scan(user_email,1,'"');
		prod_version = scan(prod_version,1,'"');
		source_event = scan(source_event,1,'"');
		PT_WaitTime = scan(PT_WaitTime,1,'"');

run;

Re: How to Output to One Record After Assiging Values

Reeza — Thu, 14 Feb 2019 20:49:30 GMT

It's not clear what this means:

I'm new as using the scan function and would like to know how to output to the record after each iteration of through the code.

Your new data set, testdata2, should have a record for each line in your source data file.

If you were trying to remove the quotes with the SCAN() function, COMPRESS() or DEQUOTE() which removes quotes.

@FollinLane wrote:

Hi,

I'm reading in a file using the code below. I'm new as using the scan function and would like to know how to output to the record after each iteration of through the code.

Thank you

data testdata2;
    infile mysource lrecl = 6807 truncover scanover;
        input
            @'"UserEmail"|"metadata_value":"' user_email $100.
			@'"ProductVersion"|"metadata_value":"' prod_version $20.
			@'"SourceEvent"|"metadata_value":"' source_event $30.
			@'"ProcessTotalWaitTime"|"metadata_value":"' PT_WaitTime $10.
			;

        user_email = scan(user_email,1,'"');
		prod_version = scan(prod_version,1,'"');
		source_event = scan(source_event,1,'"');
		PT_WaitTime = scan(PT_WaitTime,1,'"');

run;

Re: How to Output to One Record After Assiging Values

PGStats — Fri, 15 Feb 2019 05:27:26 GMT

You may extract all fields and values with the help of regular expression processing:

data have;
if not prxId then prxId + prxParse('/"metadata_name":"(.*?)"\|"metadata_value":"(.*?)"/');
infile "&sasforum.\Datasets\test.txt" truncover;
input line $1200.;
obs + 1;
length field $32 value $128;
start = 1; stop = -1;
call prxnext(prxId, start, stop, line, pos, len);
do while (pos > 0);
    field = prxPosn(prxId, 1, line);
    value = prxPosn(prxId, 2, line);
    output;
    call prxnext(prxId, start, stop, line, pos, len);
    end;
drop prxId line start stop pos len; 
run;

     obs  field                             value
------------------------------------------------------------------------------------
       1  UserEmail                         user1@mycompany.org
       1  ProductVersion                    8.0.2009.0
       1  SourceEvent                       IdleEvent|1/30/2019 12:22:11 AM
       1  ProcessTotalWaitTime              0
       1  KeyPressCount                     2
       1  ErrorKeyPressCount                0
       1  CtrlC_Count                       0
       1  CtrlV_Count                       0
       1  CtrlX_Count                       0
       1  MouseClickCount                   4
       1  MouseWheelCount                   0
       1  WindowMoveSizeCount               0
       1  ScrollCount                       0
       1  HasScreenMapping                  True
       1  IsTestScreenMapping               False
       1  IsTestUser                        False
       2  UserEmail                         user2@mycompany.org
       2  ProductVersion                    8.0.2009.0
       2  SourceEvent                       ForegroundChanged|66570|0|0|0|179953
       2  ProcessTotalWaitTime              0
.....

Re: How to Output to One Record After Assiging Values

ErikLund_Jensen — Fri, 15 Feb 2019 11:09:26 GMT

@PGStats

This is really good. Thanks!

@FollinLane

Should "output after each iteration" be interpreted as output after each variable is extracted or output after processing of each record. In the latter case, you could add a transpose step after PGStats' excellent code:


proc transpose data=have out=want let;
   by obs;
   id field;
   var value;
 run;

Re: How to Output to One Record After Assiging Values

FollinLane — Fri, 15 Feb 2019 12:58:40 GMT

Sorry for the omission but I was trying to output after processing each record. For the sake of getting too detailed I got some strange results where the output seemed to be missing records or showing a partial record. Thanks for the the additional help!

Re: How to Output to One Record After Assiging Values

FollinLane — Fri, 15 Feb 2019 14:23:57 GMT

This helps a lot. I will need to study it more. Along with what ErikLund_Jensen posted I think I have the solution.

Thank you very much!