Iterative Array Do loop based on quarters

[A_Swoosh]

Hello,

I am trying to iterate through my variables and assign a value of 0 based on the previous variable that corresponds to that quarter and year.

Here is a snippet of the data:

data have;
infile datalines4 dlm='|' missover dsd;
input id q1_2018_units q1_2018_stdunits q2_2018_units q2_2018_stdunits q3_2018_units q3_2018_stdunits q4_2018_units q4_2018_stdunits;
datalines;
1|214252|214252|149115||50000|50000|0|| 
2|12||0||50000||0|| 
3|21252|21252|||5000|.|0|| 
4|214252|.|0||5000|5000|0|| 
5|2252|.|14115||500|500|0|| 
;
run;

I want to assign a value of 0 to stdunits based on if there is a 0 in the units column for each corresponding quarter. That way I can pick out the 0 cases and compute a stdunit for those that aren't 0. To note, I have other quarters and year combinations but if I can apply to this subset then I can apply elsewhere.

For ID 2, there should be a . for q1_2018_stdunits and q3_2018_stdunits but a 0 value for q2_2018_stdunits and q4_2018_stdunits.

I was thinking of creating a var list, like below but for each quarter/year combination, to separate quarters to make it easier to go through?

/*GET LIST OF UNIT VARIABLES*/
PROC SQL NOPRINT;
CREATE TABLE VAR_NAMES AS
SELECT NAME
FROM DICTIONARY.COLUMNS
WHERE LIBNAME = 'WORK' AND MEMNAME = 'HAVE'
AND NAME CONTAINS '_units';
SELECT COMPRESS(NAME) INTO :VAR_LIST SEPARATED BY " "
FROM VAR_NAMES;
SELECT COUNT(NAME) INTO :NUM SEPARATED BY " "
FROM VAR_NAMES;
QUIT;

I think the next step would be to create an array and do loop but I'm not sure what the parameters are and what type of do statement? I also saw whichn but I have no experience with that function.

data want;
  set have;

   	ARRAY X[*] &VAR_LIST;
	array Y[*] &VAR_LIST1;

        ???

I then want to iterate through each stdunit to drop out cases where stdunit is missing so I can just examine id's with missing stdunits that require computation.

Any help would be much appreciated...

[japelin]

Is it like this?

If this is not what you intended, please be specific about what kind of data set you want.

data want(drop=i);
  set have;
  array unit[4] q1_2018_units q2_2018_units q3_2018_units q4_2018_units;
  array stdunit[4] q1_2018_stdunits q2_2018_stdunits q3_2018_stdunits q4_2018_stdunits;
  do i=1 to 4;
    if unit[i]=0 then stdunit[i]=0;
  end;
run;

Results:

[A_Swoosh]

Yes, like this but if I have several years of quarters for both units and standard units I wanted to create a var list potentially to just iterate through each one.

Second, I wanted to then create a final data set after I get the result I want in “want” that you showed where I keep only those id that have a value >0 in units column or . In stdunits column since those are the ones I need to compute.

[japelin · Accepted solution]

If I were to follow your approach, it would be possible to write the following in a loop process without writing a variable.

/*GET LIST OF UNIT VARIABLES*/
PROC SQL NOPRINT;
  CREATE TABLE VAR_NAMES AS
  SELECT NAME
    FROM DICTIONARY.COLUMNS
    WHERE LIBNAME = 'WORK' AND MEMNAME = 'HAVE';
  SELECT COMPRESS(NAME) INTO :VAR_LIST1 SEPARATED BY " "
    FROM VAR_NAMES
    WHERE NAME CONTAINS '_units';
  SELECT COMPRESS(NAME) INTO :VAR_LIST2 SEPARATED BY " "
    FROM VAR_NAMES
    WHERE NAME CONTAINS '_stdunits';
QUIT;


data want(drop=i);
  set have;
  array unit[*] &VAR_LIST1;
  array stdunit[*] &VAR_LIST2;
  do i=1 to dim(unit);
    if unit[i]=0 then stdunit[i]=0;
  end;
run;

[Kurt_Bremser]

Transpose to a long dataset, convert _NAME_ to a date, and you can extract the quarter and year.

Sort by id, quarter and year, and you can get the value of the same quarter in the previous year with the LAG function.

Long always beats wide (Maxim 19).

[A_Swoosh]

Would I not be able to do this with proc sql dictionary columns approach? Otherwise I’ll try the transpose approach.