Two different report layouts in one csv file

[jimbobob]

I have a csv file (attached) it has two different reports within it, one report named "TM Rule to SAR KPI Report" the other  named "TM Condition to SAR KPI Report", the headers are right after their names in the csv. I'm trying to split these out to two different datasets. I'm stuck on how to tell it to read the first part until it gets to a blank row or next report. I'm just getting blank tables. any help would be appreciated

DATA WORK.tm_rule_to_sar_kpi tm_condition_to_sar_kpi;
    LENGTH
        Rule_Name        $ 20
        Total_Incident_from_Rule   8
        Total_Alert_from_Rule   8
        Total_Alert_Closed_WO_Case_Rule   8
        Total_Alert_Closed_W_Case_Rule   8
        Total_Case_Closed_WO_SAR_Rule   8
        Total_Case_Closed_W_SAR_Rule   8
        Total_Alert_to_Case_Pct_Rule   8
        Total_Case_to_SAR_Pct_Rule   8
        Total_Alert_to_SAR_Pct_Rule   8

        Condition_ID     $ 50
        Severity         $ 10
        Incident_from_Condition   8
        Alert_from_Condition   8
        Alerts_Closed_WO_Case_Condition   8
        Alerts_Closed_W_Case_Condition   8
        Case_Closed_WO_SAR_Condition   8
        Case_Closed_W_SAR_Condition   8
        Alert_to_Case_Pct_Condition   8
        Case_to_SAR_Pct_Condition   8
        Alert_to_SAR_Pct_Condition   8;

    LABEL
        Rule_Name        = "Rule Name"
        Total_Incident_from_Rule = "Total Incident from Rule"
        Total_Alert_from_Rule = "Total Alert from Rule"
        Total_Alert_Closed_WO_Case_Rule = "Total Alerts Closed w/o Case from Rule"
        Total_Alert_Closed_W_Case_Rule = "Total Alerts Closed w/ Case from Rule"
        Total_Case_Closed_WO_SAR_Rule = "Total Case Closed w/o SAR from Rule"
        Total_Case_Closed_W_SAR_Rule = "Total Case Closed w/ SAR from Rule"
        Total_Alert_to_Case_Pct_Rule = "Total Alert to Case % from Rule"
        Total_Case_to_SAR_Pct_Rule = "Total Case to SAR % from Rule"
        Total_Alert_to_SAR_Pct_Rule = "Total Alert to SAR % from Rule" 

        Condition_ID     = "Condition ID"
        Incident_from_Condition = "Incident from Condition"
        Alert_from_Condition = "Alert from Condition"
        Alerts_Closed_WO_Case_Condition = "Alerts Closed w/o Case from Condition"
        Alerts_Closed_W_Case_Condition = "Alerts Closed w/ Case from Condition"
        Case_Closed_WO_SAR_Condition = "Case Closed w/o SAR from Condition"
        Case_Closed_W_SAR_Condition = "Case Closed w/ SAR from Condition"
        Alert_to_Case_Pct_Condition = "Alert to Case % from Condition"
        Case_to_SAR_Pct_Condition = "Case to SAR % from Condition"
        Alert_to_SAR_Pct_Condition = "Alert to SAR % from Condition";

    FORMAT
        Rule_Name        $CHAR20.
        Total_Incident_from_Rule BEST5.
        Total_Alert_from_Rule BEST5.
        Total_Alert_Closed_WO_Case_Rule BEST5.
        Total_Alert_Closed_W_Case_Rule BEST5.
        Total_Case_Closed_WO_SAR_Rule BEST5.
        Total_Case_Closed_W_SAR_Rule BEST5.
        Total_Alert_to_Case_Pct_Rule BEST5.
        Total_Case_to_SAR_Pct_Rule BEST5.
        Total_Alert_to_SAR_Pct_Rule BEST5. 

        Condition_ID     $CHAR50.
        Severity         $CHAR10.
        Incident_from_Condition BEST5.
        Alert_from_Condition BEST5.
        Alerts_Closed_WO_Case_Condition BEST5.
        Alerts_Closed_W_Case_Condition BEST5.
        Case_Closed_WO_SAR_Condition BEST5.
        Case_Closed_W_SAR_Condition BEST5.
        Alert_to_Case_Pct_Condition BEST5.
        Case_to_SAR_Pct_Condition BEST5.
        Alert_to_SAR_Pct_Condition BEST5.;

    INFILE 'R:\BSA Department\10. Systems (Prod)\Actimize\Prod Alert tracking\Actimize reports\2022\tm_rule_and_condition_to_sar_kpi_report_10012022-10012022.csv'
        LRECL=32767
        ENCODING="WLATIN1"
        DLM='2c'x;

		input reportsplit $3. @;

		if reportsplit = "TM R" then do;
			do until (Rule_Name = "");
		    INPUT
		        Rule_Name        : $CHAR20.
		        Total_Incident_from_Rule : ?? BEST5.
		        Total_Alert_from_Rule : ?? BEST5.
		        Total_Alert_Closed_WO_Case_Rule : ?? BEST5.
		        Total_Alert_Closed_W_Case_Rule : ?? BEST5.
		        Total_Case_Closed_WO_SAR_Rule : ?? BEST5.
		        Total_Case_Closed_W_SAR_Rule : ?? BEST5.
		        Total_Alert_to_Case_Pct_Rule : ?? BEST5.
		        Total_Case_to_SAR_Pct_Rule : ?? BEST5.
		        Total_Alert_to_SAR_Pct_Rule : ?? BEST5. ;
			output tm_rule_to_sar_kpi;
			end;
		end;
		else if reportsplit = "TM C" then do;
			do until (Rule_Name = "");
			INPUT
		        Rule_Name        : $CHAR20.
		        Condition_ID     : $CHAR50.
		        Severity         : $CHAR10.
		        Incident_from_Condition : ?? BEST5.
		        Alert_from_Condition : ?? BEST5.
		        Total_Alert_from_Rule : ?? BEST5.
		        Alerts_Closed_WO_Case_Condition : ?? BEST5.
		        Total_Alert_Closed_WO_Case_Rule : ?? BEST5.
		        Alerts_Closed_W_Case_Condition : ?? BEST5.
		        Total_Alert_Closed_W_Case_Rule : ?? BEST5.
		        Case_Closed_WO_SAR_Condition : ?? BEST5.
		        Total_Case_Closed_WO_SAR_Rule : ?? BEST5.
		        Case_Closed_W_SAR_Condition : ?? BEST5.
		        Total_Case_Closed_W_SAR_Rule : ?? BEST5.
		        Alert_to_Case_Pct_Condition : ?? BEST5.
		        Total_Alert_to_Case_Pct_Rule : ?? BEST5.
		        Case_to_SAR_Pct_Condition : ?? BEST5.
		        Total_Case_to_SAR_Pct_Rule : ?? BEST5.
		        Alert_to_SAR_Pct_Condition : ?? BEST5.
		        Total_Alert_to_SAR_Pct_Rule : ?? BEST5. ;
			output tm_condition_to_sar_kpi;
			end;
		end;
RUN;

[ballardw · Accepted solution]

This is one way:

Note that I do not have your path and really dislike like typing obnoxiously long file names so renamed to Example.csv when I downloaded the file.

DATA WORK.tm_rule_to_sar_kpi  (Keep= Rule_Name Total_Incident_from_Rule Total_Alert_from_Rule 
                              Total_Alert_Closed_WO_Case_Rule Total_Alert_Closed_W_Case_Rule 
                              Total_Case_Closed_WO_SAR_Rule Total_Case_Closed_W_SAR_Rule 
                              Total_Alert_to_Case_Pct_Rule Total_Case_to_SAR_Pct_Rule 
                              Total_Alert_to_SAR_Pct_Rule 
                              )
     tm_condition_to_sar_kpi  (keep= Rule_Name   Condition_ID Severity Incident_from_Condition 
                              Alert_from_Condition Total_Alert_from_Rule Alerts_Closed_WO_Case_Condition 
                              Total_Alert_Closed_WO_Case_Rule Alerts_Closed_W_Case_Condition 
                              Total_Alert_Closed_W_Case_Rule Case_Closed_WO_SAR_Condition 
                              Total_Case_Closed_WO_SAR_Rule Case_Closed_W_SAR_Condition 
                              Total_Case_Closed_W_SAR_Rule Alert_to_Case_Pct_Condition 
                              Total_Alert_to_Case_Pct_Rule Case_to_SAR_Pct_Condition 
                              Total_Case_to_SAR_Pct_Rule Alert_to_SAR_Pct_Condition 
                              Total_Alert_to_SAR_Pct_Rule  
                              )
;
    LENGTH
        Rule_Name        $ 20
        Total_Incident_from_Rule   8
        Total_Alert_from_Rule   8
        Total_Alert_Closed_WO_Case_Rule   8
        Total_Alert_Closed_W_Case_Rule   8
        Total_Case_Closed_WO_SAR_Rule   8
        Total_Case_Closed_W_SAR_Rule   8
        Total_Alert_to_Case_Pct_Rule   8
        Total_Case_to_SAR_Pct_Rule   8
        Total_Alert_to_SAR_Pct_Rule   8

        Condition_ID     $ 50
        Severity         $ 10
        Incident_from_Condition   8
        Alert_from_Condition   8
        Alerts_Closed_WO_Case_Condition   8
        Alerts_Closed_W_Case_Condition   8
        Case_Closed_WO_SAR_Condition   8
        Case_Closed_W_SAR_Condition   8
        Alert_to_Case_Pct_Condition   8
        Case_to_SAR_Pct_Condition   8
        Alert_to_SAR_Pct_Condition   8;

    LABEL
        Rule_Name        = "Rule Name"
        Total_Incident_from_Rule = "Total Incident from Rule"
        Total_Alert_from_Rule = "Total Alert from Rule"
        Total_Alert_Closed_WO_Case_Rule = "Total Alerts Closed w/o Case from Rule"
        Total_Alert_Closed_W_Case_Rule = "Total Alerts Closed w/ Case from Rule"
        Total_Case_Closed_WO_SAR_Rule = "Total Case Closed w/o SAR from Rule"
        Total_Case_Closed_W_SAR_Rule = "Total Case Closed w/ SAR from Rule"
        Total_Alert_to_Case_Pct_Rule = "Total Alert to Case % from Rule"
        Total_Case_to_SAR_Pct_Rule = "Total Case to SAR % from Rule"
        Total_Alert_to_SAR_Pct_Rule = "Total Alert to SAR % from Rule" 

        Condition_ID     = "Condition ID"
        Incident_from_Condition = "Incident from Condition"
        Alert_from_Condition = "Alert from Condition"
        Alerts_Closed_WO_Case_Condition = "Alerts Closed w/o Case from Condition"
        Alerts_Closed_W_Case_Condition = "Alerts Closed w/ Case from Condition"
        Case_Closed_WO_SAR_Condition = "Case Closed w/o SAR from Condition"
        Case_Closed_W_SAR_Condition = "Case Closed w/ SAR from Condition"
        Alert_to_Case_Pct_Condition = "Alert to Case % from Condition"
        Case_to_SAR_Pct_Condition = "Case to SAR % from Condition"
        Alert_to_SAR_Pct_Condition = "Alert to SAR % from Condition";

    FORMAT
        Rule_Name        $CHAR20.
        Total_Incident_from_Rule BEST5.
        Total_Alert_from_Rule BEST5.
        Total_Alert_Closed_WO_Case_Rule BEST5.
        Total_Alert_Closed_W_Case_Rule BEST5.
        Total_Case_Closed_WO_SAR_Rule BEST5.
        Total_Case_Closed_W_SAR_Rule BEST5.
        Total_Alert_to_Case_Pct_Rule BEST5.
        Total_Case_to_SAR_Pct_Rule BEST5.
        Total_Alert_to_SAR_Pct_Rule BEST5. 

        Condition_ID     $CHAR50.
        Severity         $CHAR10.
        Incident_from_Condition BEST5.
        Alert_from_Condition BEST5.
        Alerts_Closed_WO_Case_Condition BEST5.
        Alerts_Closed_W_Case_Condition BEST5.
        Case_Closed_WO_SAR_Condition BEST5.
        Case_Closed_W_SAR_Condition BEST5.
        Alert_to_Case_Pct_Condition BEST5.
        Case_to_SAR_Pct_Condition BEST5.
        Alert_to_SAR_Pct_Condition BEST5.;

    INFILE "&outpath.Example.csv"
        LRECL=32767
        ENCODING="WLATIN1"
        DLM='2c'x;

		input  @;
      Retain SectionFlag;
		if _infile_ =: "TM R" then do;
/*			do until (Rule_Name = "");*/
      /* set flag variable which section to use for input*/
         sectionflag=1;
         /* release line and skip header*/
         input / ;
      end;
      else if _infile_ =: "TM C" then do;
         sectionflag=2;
         input / ;
      end;
      else if length(_infile_)<2 then do;
         /* set flag to 0 for "blank" lines so nothing else is read*/
         sectionflag=0;
         input;
      end;
      
      if sectionflag=1 then do;
		    INPUT
		        Rule_Name        : $CHAR20.
		        Total_Incident_from_Rule : ?? BEST5.
		        Total_Alert_from_Rule : ?? BEST5.
		        Total_Alert_Closed_WO_Case_Rule : ?? BEST5.
		        Total_Alert_Closed_W_Case_Rule : ?? BEST5.
		        Total_Case_Closed_WO_SAR_Rule : ?? BEST5.
		        Total_Case_Closed_W_SAR_Rule : ?? BEST5.
		        Total_Alert_to_Case_Pct_Rule : ?? BEST5.
		        Total_Case_to_SAR_Pct_Rule : ?? BEST5.
		        Total_Alert_to_SAR_Pct_Rule : ?? BEST5. ;
			output tm_rule_to_sar_kpi;
			
		end;
		else if sectionflag=2 then do;
			INPUT
		        Rule_Name        : $CHAR20.
		        Condition_ID     : $CHAR50.
		        Severity         : $CHAR10.
		        Incident_from_Condition : ?? BEST5.
		        Alert_from_Condition : ?? BEST5.
		        Total_Alert_from_Rule : ?? BEST5.
		        Alerts_Closed_WO_Case_Condition : ?? BEST5.
		        Total_Alert_Closed_WO_Case_Rule : ?? BEST5.
		        Alerts_Closed_W_Case_Condition : ?? BEST5.
		        Total_Alert_Closed_W_Case_Rule : ?? BEST5.
		        Case_Closed_WO_SAR_Condition : ?? BEST5.
		        Total_Case_Closed_WO_SAR_Rule : ?? BEST5.
		        Case_Closed_W_SAR_Condition : ?? BEST5.
		        Total_Case_Closed_W_SAR_Rule : ?? BEST5.
		        Alert_to_Case_Pct_Condition : ?? BEST5.
		        Total_Alert_to_Case_Pct_Rule : ?? BEST5.
		        Case_to_SAR_Pct_Condition : ?? BEST5.
		        Total_Case_to_SAR_Pct_Rule : ?? BEST5.
		        Alert_to_SAR_Pct_Condition : ?? BEST5.
		        Total_Alert_to_SAR_Pct_Rule : ?? BEST5. ;
			output tm_condition_to_sar_kpi;
		end;
RUN;

You program has a bit of "when does Input execute" logic and the Do Until wasn't behaving as you expected.

I set a Retained flag variable that can be used to see which section of input code should execute. So when the boundary value is encountered the flag is set, and since it is Retained that value will stay so until one of the other boundary conditions is met. The Input after setting the flag is to release the current line and the / means "and input from the following line" which skips us past the column header row. This bit about releasing why you code had issues as when it found the boundary for the section the next input was from that same row.

The _infile_ is an automatic variable, up to 32K characters if the LRECL is long enough, created by SAS on any Input statement. So you can test bits of that entire row. I am using the =: as "if the line begins with".

With the retained flag value then each row is encountered, retested for the boundaries, and if not one of them executes the Input one time for the next row (remember the / that skipped the variable column headings).

This code should actually work with multiple blocks if you have such.

I added data set option KEEP statements to only keep the variables that appear on the Input section for each output. Having the other variables may be confusing since they would be missing. If you actually want all the variables in both sets then remove the Keep statements though you will then likely want to DROP the Sectionflag variable that I added.

I have to say you are willing to accept longer variable names then I'm willing to type very often.

This is actually a fairly simple file.

Early in my SAS experience I was reading text report files that had weather data from multiple sites with one row per day of the month and columns that related to the month of the year with some columns actually being a high and low temperature. So I had parse the heading of the report to identify which site, read further to see which Year was represented and then determine if the report was temperature,  precipitation total or other measure. For 12 sites and 25 years. And then stitch them together by date so all the daily measures were one record per site per date.

[jimbobob]

thanks @ballardw , work perfectly

[Tom]

No need for looping.  The data step does that already.  But you do need to remember which file you are reading.

Also there is no need to attach formats to ANY of the variables you show.  SAS does not need special instructions for display strings or numbers.

And there is no need to use special informats when reading the values from the CSV file either.

You will want to use KEEP= dataset options so you can make sure that each output dataset has only the variables it should.

You should either prefix the work dataset names with WORK. every time or never.  Don't mix them like you did in your DATA statement.

DATA tm_rule_to_sar_kpi (keep=Rule_Name -- Total_Alert_to_SAR_Pct_Rule)
     tm_condition_to_sar_kpi (keep=Rule_Name Condition_ID -- Alert_to_SAR_Pct_Condition)

;
    LENGTH
        Rule_Name        $ 20
        Total_Incident_from_Rule   8
        Total_Alert_from_Rule   8
        Total_Alert_Closed_WO_Case_Rule   8
        Total_Alert_Closed_W_Case_Rule   8
        Total_Case_Closed_WO_SAR_Rule   8
        Total_Case_Closed_W_SAR_Rule   8
        Total_Alert_to_Case_Pct_Rule   8
        Total_Case_to_SAR_Pct_Rule   8
        Total_Alert_to_SAR_Pct_Rule   8

        Condition_ID     $ 50
        Severity         $ 10
        Incident_from_Condition   8
        Alert_from_Condition   8
        Alerts_Closed_WO_Case_Condition   8
        Alerts_Closed_W_Case_Condition   8
        Case_Closed_WO_SAR_Condition   8
        Case_Closed_W_SAR_Condition   8
        Alert_to_Case_Pct_Condition   8
        Case_to_SAR_Pct_Condition   8
        Alert_to_SAR_Pct_Condition   8
    ;

    LABEL
        Rule_Name        = "Rule Name"
        Total_Incident_from_Rule = "Total Incident from Rule"
        Total_Alert_from_Rule = "Total Alert from Rule"
        Total_Alert_Closed_WO_Case_Rule = "Total Alerts Closed w/o Case from Rule"
        Total_Alert_Closed_W_Case_Rule = "Total Alerts Closed w/ Case from Rule"
        Total_Case_Closed_WO_SAR_Rule = "Total Case Closed w/o SAR from Rule"
        Total_Case_Closed_W_SAR_Rule = "Total Case Closed w/ SAR from Rule"
        Total_Alert_to_Case_Pct_Rule = "Total Alert to Case % from Rule"
        Total_Case_to_SAR_Pct_Rule = "Total Case to SAR % from Rule"
        Total_Alert_to_SAR_Pct_Rule = "Total Alert to SAR % from Rule" 

        Condition_ID     = "Condition ID"
        Incident_from_Condition = "Incident from Condition"
        Alert_from_Condition = "Alert from Condition"
        Alerts_Closed_WO_Case_Condition = "Alerts Closed w/o Case from Condition"
        Alerts_Closed_W_Case_Condition = "Alerts Closed w/ Case from Condition"
        Case_Closed_WO_SAR_Condition = "Case Closed w/o SAR from Condition"
        Case_Closed_W_SAR_Condition = "Case Closed w/ SAR from Condition"
        Alert_to_Case_Pct_Condition = "Alert to Case % from Condition"
        Case_to_SAR_Pct_Condition = "Case to SAR % from Condition"
        Alert_to_SAR_Pct_Condition = "Alert to SAR % from Condition"
    ;

    INFILE original
        dsd truncover 
    ;

    input @ ;
    if _infile_ = ' ' then delete;
    retain csv ;
    if _infile_ in ("TM Rule to SAR KPI Report" "TM Condition to SAR KPI Report") then do;
      if _infile_ = "TM Rule to SAR KPI Report" then csv=1;
      else csv=2;
      input; 
      input;
      delete;
    end;
    if csv=1 then do;
      input Rule_Name -- Total_Alert_to_SAR_Pct_Rule;
      output tm_rule_to_sar_kpi;
    end;
    else if csv=2 then do;
       input Rule_Name Condition_ID -- Alert_to_SAR_Pct_Condition;
       output tm_condition_to_sar_kpi;
    end;
run;

[jimbobob]

Thank @Tom good to know I don;t need to list out each format or informat. thank you.

[Ksharp]

%let file= C:\Users\1\Downloads\tm_rule_and_condition_to_sar_kpi_report_10012022-10012022.csv ;




filename x "&file.";
filename have1 temp;
filename have2 temp;
data _null_;
infile x;
file have1;
input;
retain have1 0;
if _infile_='TM Condition to SAR KPI Report' then stop;
if have1 and not missing(_infile_) then put _infile_;
if _infile_='TM Rule to SAR KPI Report' then have1=1;
run;
data _null_;
infile x;
file have2;
input;
retain have2 0;
if have2 then put _infile_;
if _infile_='TM Condition to SAR KPI Report' then have2=1;
run;

proc import datafile=have1 out=have1 dbms=csv replace;
run;
proc import datafile=have2 out=have2 dbms=csv replace;
run;

[jimbobob]

Thanks @Ksharp another great solution