topic Convert Array And Do while to SQL in SAS Programming

Convert Array And Do while to SQL

David_Billa — Wed, 01 Jun 2022 18:11:13 GMT

I'm unable to understand the following code which was written by other programmer. Can someone help me understand this code in simple words and also I'd like to know if this can be converted to Proc SQL?

DATA ATT(DROP=J);*(DROP=FCST:);
  MERGE INVENT(IN=I)
        FCST_WIDE(IN=F);
  BY LOC MATERIAL;
  
  NEW_HAND = COALESCE(HAND,0);
  J=1;
  IF FIRST.MATERIAL THEN 
  DO;
    ARRAY FCT(*) &FCST_D2.;
  END;
  
  DO WHILE (NEW_HAND >0 and J <= DIM(FCT));
    
    FCT[J] = COALESCE(FCT[J],0);
    
    IF INPUT(SUBSTR(VNAME(FCT[J]),5),mmddyy10.)<=STOPSHIP_DT THEN 
    DO;
      
      IF NEW_HAND > FCT[J] THEN 
      DO;
        NEW_HAND = NEW_HAND-FCT[J];
        FCT[J]=0;
      END;
      ELSE IF FCT[J] > NEW_HAND THEN 
      DO;
        FCT[J] = FCT[J]-NEW_HAND;
        NEW_HAND=0;
      END;
      ELSE 
      DO;
        FCT[J]=0;
        NEW_HAND=0;
      END;
    END;
    J+1;
  END;
  
  IF LAST.MATERIAL THEN
  DO;
    REM_DEMAND = SUM(of FCST:);
  END;
RUN;

Re: Convert Array And Do while to SQL

ballardw — Wed, 01 Jun 2022 18:27:28 GMT

Which part(s) do you not understand? Without data, such as which variable(s) come from which data set(s) it can be a tad misleading to guess exactly. Especially since there is a macro variable without a definition apparently providing variable names.

If you have data look at the start and end result. That should help a lot.

ARRAY and SQL not going to happen. You have to explicitly code every thing for each variable. Since the attached code uses BY group processing to start/stop and summarize with First. and Last. processing, which requires processing records in a specific order, not to mention the way MERGE might differ from any type of SQL join then unless you have a great deal of time.

Apparently the data violates the best practice of not putting data in the variable names as there is a bit that is pulling date information from the variable names.

Since the data has data inside variable names it may be possible to extract that date information so that you have Date and value pairs and completely restructure the source data sets to a more normalized form and then join the data. Maybe. That would then allow possibly allow a join on dates as well as the LOC and Material variables. Again maybe.

Re: Convert Array And Do while to SQL

David_Billa — Wed, 01 Jun 2022 18:33:17 GMT

I'd like to understand array part and do while loop

Re: Convert Array And Do while to SQL

PaigeMiller — Wed, 01 Jun 2022 18:59:32 GMT

@David_Billa wrote:
I'd like to understand array part and do while loop

What don't you understand about them?

Re: Convert Array And Do while to SQL

Kurt_Bremser — Wed, 01 Jun 2022 19:12:06 GMT

Maxim 1: Read the Documentation.

ARRAY

DO WHILE

The code suggests that there is data in structure (dates in variable names), which is always a VERY BAD IDEA and the main cause why array processing is needed. And this array processing is the #1 reason why you can't do this in SQL

This

IF FIRST.MATERIAL THEN 
  DO;
    ARRAY FCT(*) &FCST_D2.;
  END;

is total bogus and misleading to the unwary SAS novice, as ARRAY is a declarative statement which cannot be executed conditionally.

To get this mess straight, we need to see proper and useful (read: data step with datalines) examples of the datasets.

Re: Convert Array And Do while to SQL

Reeza — Wed, 01 Jun 2022 21:00:40 GMT

Can you provide an example of the input data? It's not always straightforward to convert when the code is dynamic.

Just a guess, it looks like an expansion of data, possibly expanding a status of something over time.
Definitely not something I'd try with SQL.