SELECT WHEN statement using arrays and variables

[Astounding]

This is still several steps away from being finished.  Here's the first change, modifying this step:

113 Proc sql noprint;

114 Select cats("'",grp,"'")

115 into :grp separated by ' '

116 from PROJEKT.PODZIALY_ZNAKOWE order by zmienna;

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.

117 quit;

As was done in the earlier SQL, this one should also read from the EXPANDED data set.  Since that data set is in the proper order, there is no need for an ORDER BY clause here.  Then the values listed by %PUT &GRP should match up with the values in the earlier %PUT statement.

[Margrett]

Oh sorry. I missed that spot! What's now?

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

61

62 PROC surveyselect data=PROJEKT.vin

63 out=vin_sample

64 method=srs sampsize=3000;/* simple random sample - srs */

65 run;

NOTE: The data set WORK.VIN_SAMPLE has 3000 observations and 9 variables.

NOTE: PROCEDURE SURVEYSELECT used (Total process time):

real time 0.34 seconds

cpu time 0.15 seconds

66

67 data expanded;

68 set PROJEKT.PODZIALY_ZNAKOWE;

69 do k=1 to countw(war, ',');

70 war_expanded = scan(war, k, ',');

71 output;

72 end;

73 drop war k;

74 rename war_expanded=war;

75 run;

NOTE: There were 18 observations read from the data set PROJEKT.PODZIALY_ZNAKOWE.

NOTE: The data set WORK.EXPANDED has 21 observations and 3 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.01 seconds

76

77 /* sortuje tabele po zmiennych i warunkach */

78 proc sort data=expanded out=expanded;

79 by zmienna war;

80 quit;

NOTE: There were 21 observations read from the data set WORK.EXPANDED.

NOTE: The data set WORK.EXPANDED has 21 observations and 3 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

81

82

83 /*czytam wszystkie wiersze w kolejnosci, zeby odpowiadaly sobie nawzajem przy warunkach */

84 /* zmienna */

85 Proc sql noprint;

86 Select zmienna into :zm separated by ' '

87 from expanded order by zmienna;

88 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

89

90 %put &zm.;

app_char_branch app_char_branch app_char_branch app_char_branch app_char_branch app_char_cars app_char_cars app_char_city

app_char_city app_char_city app_char_city app_char_gender app_char_gender app_char_home_status app_char_home_status

app_char_job_code app_char_job_code app_char_job_code app_char_marital_status app_char_marital_status app_char_marital_status

91 %let ilosc=&sqlobs; /*sqlobs - liczba obserwacji z proc sql */

92 %put &ilosc.;

21

93

94 /* warunek */

95 Proc sql noprint;

96 /* Select cats('"',war,'"') */

97 Select war into :war separated by ' '

98 from expanded order by zmienna;

NOTE: The query as specified involves ordering by an item that doesn't appear in its SELECT clause.

99 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

100

101 %put &war.;

'Computers' 'DiY' 'Empty' 'Fenitures' 'Radio-TV' 'No' 'Owner' 'Big' 'Large' 'Medium' 'Small' 'Female' 'Male' 'Owner' 'Rental'

'Owner company' 'Permanent' 'Retired' 'Divorced' 'Maried' 'Widowed'

102

103 /* grupa */

104 Proc sql noprint;

105 Select cats("'",grp,"'")

106 into :grp separated by ' '

107 from expanded;

108 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

109

110 %put &grp.;

'1' '2' '3' '1' '1' '1' '2' '1' '3' '1' '2' '1' '2' '1' '2' '1' '3' '2' '3' '1' '2'

111

112

113 /* tworze nowa tabele na bazie starej z vin znakowym uzywajac sformulowania select when */

114 data vin_grp(drop=i);

115 set vin_sample;

116

117 /*tworze tablice ktore przechowaja mi zmienne stworzone przez proc sql */

118 array zmienna (&ilosc.) &zm.;

119 array warunek (&ilosc.) $100 _temporary_ (&war.);

120 array grupa (&ilosc.) $1 _temporary_ (&grp.);

121

122 put zmienna(1);

123 put zmienna(5);

124

125

126 /* lece w petli ze wzgledu na tablice */

127 do i = 1 to 5;

128

129 /* wyswietlam w logach wartosci, zeby sledzic co jest nie tak */

130 put warunek(i);

131 put zmienna(i);

132 put grupa(i);

133

134 select (zmienna(i));

135 when (warunek(i)) zmienna(i)=grupa(i); /*jezeli zachodzi warunek to zmienna przyjmuje wartosc grupy */

136

137 /* jesli warunek nie zajdzie, to w logach wyrzuca nam informacje o problemie z obserwacja */

138 otherwise put 'PROBLEM OBSERVATION';

139 end;

140 end;

141 run;

Empty

Empty

Computers

Empty

1

PROBLEM OBSERVATION

DiY

Empty

2

PROBLEM OBSERVATION

Empty

Empty

3

Fenitures

3

1

PROBLEM OBSERVATION

Radio-TV

3

1

PROBLEM OBSERVATION

Empty

Empty

Computers

Empty

1

PROBLEM OBSERVATION

DiY

Empty

2

(...) 

[Astounding]

Next:  Any place the number 5 is being used, shouldn't that be replaced with &SQLOBS?

After that:  Accept the fact that requiring the use of SELECT adds unnecessarily to the complexity of the program.  Decide if you have to keep it rather than eliminate it.

[Margrett]

Yes, you're right. I change the code to check something and I forgot to change it again after while. Now it's "&sqlobs" there.

Unfortunately I have to use 'select when' statement, so I can't throw that away 😞

EDIT: But If you have time and you want to you can also show me  a different method - 'less heavy' for the processor. But on my classes I have to use 'select when'

[Astounding]

Some replacement logic for the final DO loop, omitting SELECT:

do i=1 to &ilosc.;

   if zmienna{i} = warunek{i} then zmienna{i} = grupa{i};

end;

do i=1 to &ilosc.;

if zmienna{i} not in ('1' '2' '3') then put 'PROBLEM OBSERVATION:  element ' i;

end;

You can put the SELECT logic inside the DO loop, but it complicates the code, and might print PROBLEM OBSERVATION extra times.

All of those comments are subject to testing, which I leave to you.

[Margrett]

Yes it's working. Thanks. But I still don't understand why if I using select statement there are some problems with array called zmienna(i) ? Now everything is allright. Hmn.

PS; Maybe it will be a stupid question but is there different statement using "when" than "select when" statement? My teacher said we should used "when" ... I thought he meant 'select when'. But you said that it's way more complicated and now I wounder....

[Astounding]

WHEN can also be used in PROC SQL, in a CASE clause.  But that is very different.

SELECT has two forms:

SELECT;

SELECT (value);

Either form uses WHEN statements to find a match.  The first one has to add a comparison such as WHEN (a=b) to find a match.  The second form does not use a comparison:  WHEN (a) to see whether A is equal to VALUE.

Also note, in my previous post there is no good reason to use two DO loops.  Both statements could be included in the same DO loop.

[Margrett]

Thank you for all your help. Now I will try to modify this code for numeric dataset and conditions :]

[Margrett]

My numeric dataset is bit of a challenge. I thought I'll just change a little bit your code but conditions are much different that in character dataset. Could you give last look on that? 

I can't divide the condition values cause it's the whole statement that I need. If I use the extra apostrophes then nothing happens. 

My code:

proc sort data=PROJEKT.PODZIALY_NUMERYCZNE out=PROJEKT.PODZIALY_NUMERYCZNE;
by zmienna grp;
quit;

/* zmienna */
Proc sql noprint;
Select zmienna into :zm2 separated by ' '
from PROJEKT.PODZIALY_NUMERYCZNE;
quit;

%put &zm2.;

/* warunek */
Proc sql noprint;
Select cats("'",war,"'")
into :war2 separated by ' '
from PROJEKT.PODZIALY_NUMERYCZNE;
quit;

%put &war2.;

/* grupa */
Proc sql noprint;
Select cats("'",grp,"'")
into :grp2 separated by ' '
from PROJEKT.PODZIALY_NUMERYCZNE;
quit;

%put &grp2.;

data vin_grp2(drop=i);
          set PROJEKT.vin2_sample;

                        array warunek (&ilosc2) $100 _temporary_ (&war2.);
                        array grupa (&ilosc2) $1 _temporary_ (&grp2.);
                        array zmienna (&ilosc2) &zm2.;

                       put warunek{1};
                       put warunek{3};

                  do i = 1 to &sqlobs;

                            warunek{i}=tranwrd(warunek{i}, "'", "");
                            if warunek{i} then zmienna{i}=grupa{i};
                  end;

run;

Information from the log:

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

120:8 120:24

2.3491298167 < act12_n_arrears <= 2.3491298167

2.3491298167 < act12_n_arrears <= 2.3491298167

NOTE: Invalid numeric data, '2.3491298167 < act12_n_arrears <= 2.3491298167' , at line 120 column 8.

(...)

[Reeza]

Rant: Are you 100% positive you need to use this approach? I know you said it was a class requirement earlier but it's a really bad way to design code and you're creating a monolith that will be hard to modify and debug in the future. Using Formats or lookup tables is a lot cleaner in the long run. If you plan to ever modify or use this program again, I would strongly recommend a different appraoch.

End Rant.

Somethings to keep in mind:

1. Your arrays are currently declared as characters arrays and arrays cannot hold mixed types, so you need one for numeric variables and one for character variables. 

                        array warunek (&ilosc2) $100 _temporary_ (&war2.);
                        array grupa (&ilosc2) $1 _temporary_ (&grp2.);

2. Rather than CATS with the quotation marks use the QUOTE() function, it can do either single or double quotes, check the docs if you need to use single quotes. 

Select cats("'",grp,"'")

3. You don't need multiple queries to create the two macro variable lists, you can do that in a single PROC SQL query.

4. Your code here isn't using Select WHEN so it seems like this doesn't align with that methodology either, so see my Rant above. 

5. This (&ilosc2) isn't defined in the code you've posted, it probably needs to be calculated as well, for each recoding process. 

6. Comment your code so you understand what each step is doing, and we understand what you're intending each step to do. Otherwise my assumption is the code posted is correct and does what you want.

[Margrett]

I changed my code and forgot about 'when' statement because of running out of time. I thought I will give the results to my colleague. Because she needs to work on that to finished this project. And in meanwhile I will rethink this part of the code. Because I should use select when statement or I don't know something connected with "when"...  Oh my! This project is killing me... I had only 5 classes about SAS - really basic stuffs. I write my code using information from the net, blogs etc. I don't have any experience working with SAS. So...

Forget about my code if you think it's terrible and let's start from the beginning. I have two datasets: one main dataset (vin2) and second one with conditions (podzialy_numeryczne). I want to change my main dataset using the conditions from the second dataset. How can I do that in the most simple way?