topic Re: Converting report into CSV in SAS Programming

Converting report into CSV

gskn — Mon, 11 Feb 2019 06:14:22 GMT

Hi All,

I am trying to convert a report into a csv file.

the report in not consistent and i am having problem to break it down.

Below is sample report:

REC0168319KAYING        MANAGER  1001
    30227     INCORRECT DEPARTMENT ID
REC0164567WALSH         HR       4001
    30227     INCORRECT EMPLOYEE ID
REC0168319KAYING        MANAGER  1001
    30227     INCORRECT DEPARTMENT ID
REC0168934JOHN          SALES    1001
    30227     INCORRECT DEPARTMENT ID
    29040     INCORRECT EMPLOYEE ID
REC0168345BLAZE         ANALYST  2001
REC0168212WADE          HR       4001
REC0168458JONAS         ANALYST  2001
    30227     INCORRECT DEPARTMENT ID
    29040     INCORRECT EMPLOYEE ID
    30227     INCORRECT DEPARTMENT ID
    29040     INCORRECT EMPLOYEE ID
    30227     INCORRECT DEPARTMENT ID

the record layout:

record_id     1-5
emp_id        6-10
emp_name 11-24
job_id          25-33
dept_id        34-37

the report has record followed by error/reject comment.

There are few instance with more than 1 error/reject comment. and some with multiple record followed by multiple error comment.

output layout:

record_id~emp_id~emp_name~job_id~dept_id~error_msg1~error_msg2

I am not able to handle when there are multiple records followed by multiple error messages.

can anyone help

Re: Converting report into CSV

andreas_lds — Mon, 11 Feb 2019 06:19:43 GMT

I don't understand what you have and what you want as output. Please post data in usable form: a data-step creating the dataset that you want to export. Then add what you exactly expect as output.

Re: Converting report into CSV

gskn — Mon, 11 Feb 2019 06:58:43 GMT

@andreas_lds wrote:
I don't understand what you have and what you want as output. Please post data in usable form: a data-step creating the dataset that you want to export. Then add what you exactly expect as output.

we send record to update the database. The records that were not updated were returned to us as a report.

The sample report below. It has the update record (which was rejected)followed by error message.

REC0168319KAYING        MANAGER  1001                       ==> actual update record #1
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#1
REC0164567WALSH         HR       4001                       ==> actual update record #2     
    30227     INCORRECT EMPLOYEE ID                         ==> unique error number & error message.#2
REC0168319KAYING        MANAGER  1001                       ==> actual update record #3     
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#3
REC0168934JOHN          SALES    1001                       ==> actual update record #4     
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#4
    29040     INCORRECT EMPLOYEE ID                         ==> unique error number & error message.#4
REC0168345BLAZE         ANALYST  2001                       ==> actual update record #5     
REC0168212WADE          HR       4001                       ==> actual update record #6     
REC0168458JONAS         ANALYST  2001                       ==> actual update record #7     
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#5
    29040     INCORRECT EMPLOYEE ID                         ==> unique error number & error message.#5
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#6
    29040     INCORRECT EMPLOYEE ID                         ==> unique error number & error message.#6
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#7

I am trying to breakdown this report into a delimited file. The report is not consistent pattern.

Re: Converting report into CSV

andreas_lds — Mon, 11 Feb 2019 07:32:09 GMT

Again: please post test-data in usable form. Is that report a text-file?

Re: Converting report into CSV

Patrick — Mon, 11 Feb 2019 10:07:55 GMT

@gskn

Below code should give you the idea how to read such data structures.

/**
the record layout:
  record_id 1-5
  emp_id    6-10
  emp_name  11-24
  job_id    25-33
  dept_id   34-37
**/

data 
  emp_data(keep=record_id rec_no emp_id emp_name job_id dept_id)
  emp_load_msg(keep=record_id rec_no message)
  ;
  infile datalines truncover;
  attrib record_id length=$5 rec_no length=$8;
  retain record_id;
  input @;
  if not missing(substrn(_infile_,1,1)) then 
    do;
      input 
        record_id $ 1-5
        emp_id    $ 6-10
        emp_name  $ 11-24
        job_id    $ 25-33
        dept_id   $ 34-37
        ;
      rec_no=scan(trim(_infile_),-1,'#');
      output emp_data;
    end;
    else
    if anydigit(substrn(_infile_,5,1)) then
      do;
        input
          @5 message $200.
          ;
        rec_no=scan(trim(_infile_),-1,'#');
        output emp_load_msg;
      end;

datalines;
REC0168319KAYING        MANAGER  1001                       ==> actual update record #1
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#1
REC0164567WALSH         HR       4001                       ==> actual update record #2     
    30227     INCORRECT EMPLOYEE ID                         ==> unique error number & error message.#2
REC0168319KAYING        MANAGER  1001                       ==> actual update record #3     
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#3
REC0168934JOHN          SALES    1001                       ==> actual update record #4     
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#4
    29040     INCORRECT EMPLOYEE ID                         ==> unique error number & error message.#4
REC0168345BLAZE         ANALYST  2001                       ==> actual update record #5     
REC0168212WADE          HR       4001                       ==> actual update record #6     
REC0168458JONAS         ANALYST  2001                       ==> actual update record #7     
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#5
    29040     INCORRECT EMPLOYEE ID                         ==> unique error number & error message.#5
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#6
    29040     INCORRECT EMPLOYEE ID                         ==> unique error number & error message.#6
    30227     INCORRECT DEPARTMENT ID                       ==> unique error number & error message.#6
;
run;

Re: Converting report into CSV

gskn — Mon, 11 Feb 2019 13:34:36 GMT

thank you for reply. The report does not have record number at the end.

I included it to explain the report structure.

Below is actual report without record# at the end.

REC0168319KAYING        MANAGER  1001      
    30227     INCORRECT DEPARTMENT ID      
REC0164567WALSH         HR       4001      
    30227     INCORRECT EMPLOYEE ID        
REC0168319KAYING        MANAGER  1001      
    30227     INCORRECT DEPARTMENT ID
REC0168934JOHN          SALES    1001      
    30227     INCORRECT DEPARTMENT ID
    29040     INCORRECT EMPLOYEE ID
REC0168345BLAZE         ANALYST  2001      
REC0168212WADE          HR       4001      
REC0168458JONAS         ANALYST  2001      
    30227     INCORRECT DEPARTMENT ID
    29040     INCORRECT EMPLOYEE ID
    30227     INCORRECT DEPARTMENT ID
    29040     INCORRECT EMPLOYEE ID
    30227     INCORRECT DEPARTMENT ID

Re: Converting report into CSV

gskn — Mon, 11 Feb 2019 14:50:01 GMT

The report is in text file and it is input. I need to convert a report into a usable form (a delimited file).

Input report:

REC0168319KAYING        MANAGER  1001      
    30227     INCORRECT DEPARTMENT ID      
REC0164567WALSH         HR       4001      
    29040     INCORRECT EMPLOYEE ID        
REC0168319KAYING        MANAGER  1001      
    30227     INCORRECT DEPARTMENT ID
REC0168934JOHN          SALES    1001      
    30227     INCORRECT DEPARTMENT ID
    29040     INCORRECT EMPLOYEE ID
REC0168345BLAZE         ANALYST  2001      
REC0168212WADE          HR       4001      
REC0168458JONAS         ANALYST  2001      
    30227     INCORRECT DEPARTMENT ID
    29040     INCORRECT EMPLOYEE ID
    30227     INCORRECT DEPARTMENT ID
    29040     INCORRECT EMPLOYEE ID
    30227     INCORRECT DEPARTMENT ID

Output:

REC_ID~EMP_ID~EMP_NAME~EMP_ROLE~DEPT_ID~Error01~Error02~
REC01~68319~KAYING ~MANAGER ~1001~30227 INCORRECT DEPARTMENT ID~
REC01~64567~WALSH ~HR ~4001~29040 INCORRECT EMPLOYEE ID~ 
REC01~68319~KAYING        ~MANAGER  ~1001~30227     INCORRECT DEPARTMENT ID~
REC01~68934~JOHN          ~SALES    ~1001~30227     INCORRECT DEPARTMENT ID~29040     INCORRECT EMPLOYEE ID~
REC01~68345~BLAZE         ~ANALYST  ~2001~30227     INCORRECT DEPARTMENT ID~29040     INCORRECT EMPLOYEE ID~
REC01~68212~WADE          ~HR       ~4001~30227     INCORRECT DEPARTMENT ID~29040     INCORRECT EMPLOYEE ID~
REC01~68458~JONAS         ~ANALYST  ~2001~30227     INCORRECT DEPARTMENT ID~

Re: Converting report into CSV

Patrick — Mon, 11 Feb 2019 20:16:50 GMT

@gskn

The values for record_id you give us are always the same. Not sure if that's just because it's not representative sample data or not.

What you need is something that ties the error messages to the data records. If the error messages always below to the latest data record then ideally just use record_id (if unique per data record) or create your own id - which could simply be a counter increased by 1 whenever you read a data record.

Else: I believe the code I've already posted should give you sufficient guidance to work out the last details yourself.