topic Re: I want to select alternate columns in my data set. in SAS Programming

I want to select alternate columns in my data set.

Discaboota — Tue, 07 May 2024 13:13:23 GMT

DATA ALT;
    SET RCA.CLOSING_STATUS_TEST;
    ARRAY COLS[*] _CHARACTER_; 
    ARRAY COLS_[*] _CHARACTER_;
    DO I = 1 TO DIM(COLS);
        IF MOD(I, 2) = 1 THEN 
            COLS_[(I+1)/2] = COLS[I]; 
    END;
    DROP I;
RUN;

Hi, I have been trying to select the alternate columns.
If I am using this code then my third column's data shifts to second column and second's values are disappearing.
I want to select alternate columns from my data set and my column names are in following:
LOAN_NO, PRODUCT, FINAL_STATUS_05MAY24,DUE_N_TAG_05MAY24, FINAL_STATUS_06MAY24,DUE_N_TAG_06MAY24, FINAL_STATUS_07MAY24,DUE_N_TAG_07MAY24,... and so on.
I want to create a table selecting columns:-

LOAN_NO, FINAL_STATUS_05MAY24, FINAL_STATUS_06MAY24, FINAL_STATUS_07MAY24... and so on.
If there is any alternate approach for making the selection, please advise me or please help with the given array code.

Re: I want to select alternate columns in my data set.

maguiremq — Tue, 07 May 2024 13:20:35 GMT

Help me understand, please -- you want the following columns in your output?

LOAN_NO, PRODUCT, FINAL_STATUS_05MAY24,DUE_N_TAG_05MAY24, FINAL_STATUS_06MAY24,DUE_N_TAG_06MAY24, FINAL_STATUS_07MAY24,DUE_N_TAG_07MAY24

Is there any way you could show us what you want at the end?

Thank you!

Re: I want to select alternate columns in my data set.

Discaboota — Tue, 07 May 2024 13:33:58 GMT

I want columns as following :-
LOAN_NO, FINAL_STATUS_05MAY24, FINAL_STATUS_06MAY24, FINAL_STATUS_07MAY24,
FINAL_STATUS_08MAY24,
FINAL_STATUS_09MAY24,
FINAL_STATUS_10MAY24,
and so on...

Re: I want to select alternate columns in my data set.

maguiremq — Tue, 07 May 2024 13:36:58 GMT

How many dates do you have included in this report? Is this run daily or weekly? Monthly?

The dates would need to be created dynamically depending on the above.

I can't tell if this is a retroactive lookback (e.g., this week looks at last week, etc.) to create those date variables.

Re: I want to select alternate columns in my data set.

ballardw — Tue, 07 May 2024 13:56:13 GMT

One of the problems that you may be encountering is the way the variables are presented using the _CHARACTER_ in a list.

You might run this code:

DATA _null_;
    SET RCA.CLOSING_STATUS_TEST (obs=1);
    ARRAY COLS[*] _CHARACTER_; 
    DO I = 1 TO DIM(COLS);
       temp= vname(cols[i]);
       put i= temp=  ;

    END;
RUN;

to show the order of the variables actually processed. May get a surprise. The VNAME function returns the name of a variable used in an array in this case. The log will show something like (using SASHELP.CARS as input):

I=1 temp=Make
I=2 temp=Model
I=3 temp=Type
I=4 temp=Origin
I=5 temp=DriveTrain

Unless all of your _CHARACTER_ variables are defined with the exact same length you general approach was very likely to have truncated values somewhere when you assign a longer value to a variable with a shorter length.

To select the Final_status variables:

data want;
   set rca.closing_status_test;
   keep loan_no Product Final_Status: ;
run;

The colon immediately following part of a variable name is one of the list building tools in SAS. It means use all the variables whose names start with Final_status in this case.

Variable names like FINAL_STATUS_05MAY24,DUE_N_TAG_05MAY24 often indicate someone is attempting to use a spreadsheet type of thinking, and typically a source of data, that is poor for general SAS processing.

Better would be to restructure your data so that you have, in addition to identification variables something that looks like FINAL_STATUS, DUE_N_TAG, and a Date variable with value of 05May2024 (and 06 May, 07 May etc) with one observation per date.

Then you would select the observations for analysis or reporting based on the range of the date values of interest.

Something like (pretty obviously untested as I don't have your data):

data reshaped;
   set  rca.closing_status_test;
   array f (*) FINAL_STATUS_05MAY24 FINAL_STATUS_06MAY24 FINAL_STATUS_07MAY24;
   array d (*) DUE_N_TAG_05MAY24    DUE_N_TAG_06MAY24    DUE_N_TAG_07MAY24;
   do i= 1 to dim(f);
      FinalStatus= f[i];
      Due = d[i];
      date = input(substr(vname(f[i]),14),date7.);
      output;
   end;
   format date date9.;
   keep Loan_no Product <other variable as needed> FinalStatus Due Date;
run;

Then once you have such a data set you would select using something like:

Proc print data=reshaped;
   where date ge '06MAY2024'd;
run;

/*or */

Proc print data=reshaped;
   where '06May2024'd le date le '07MAY2024'd;
run;

For the few procedures that don't honor a Where statement you can use where in a data set option.

I would suggest actually rereading the data to avoid such structure and variable names if possible.

Re: I want to select alternate columns in my data set.

Discaboota — Wed, 08 May 2024 04:50:20 GMT

The dates will be increasing from now on and if I need to call the query a month from now to select the column:
LOAN_NO, FINAL_STATUS_05MAY24, FINAL_STATUS_06MAY24, FINAL_STATUS_07MAY24,
FINAL_STATUS_08MAY24,
FINAL_STATUS_09MAY24,
FINAL_STATUS_10MAY24,
.
.
FINAL_STATUS_31MAY24.

In this, all I want is to select column by sequence 1,3,5,7,9,...,n
till the date of requirement which could be n .

Re: I want to select alternate columns in my data set.

SASKiwi — Wed, 08 May 2024 05:03:30 GMT

You really, really should consider reshaping your data as suggested by @ballardw .

If your table contained LOAN_NO, FINAL_STATUS_DATE and FINAL_STATUS (a row per loan per date), you would be adding a row for each new date, not creating a new column for each new date. This would simplify your logic a lot and most like improve processing efficiency as well.

Re: I want to select alternate columns in my data set.

Tom — Wed, 08 May 2024 16:16:29 GMT

@Discaboota wrote:
I want columns as following :-
LOAN_NO, FINAL_STATUS_05MAY24, FINAL_STATUS_06MAY24, FINAL_STATUS_07MAY24,
FINAL_STATUS_08MAY24,
FINAL_STATUS_09MAY24,
FINAL_STATUS_10MAY24,
and so on...

That seems trivial to me and has NOTHING to with the ORDER of the variables in the dataset, just the NAME of the variables.

data want;
  set have;
  keep loan_no final_status: ;
run;