SELECT WHEN statement using arrays and variables

[Margrett]

Okay, thank you. I will work on that in the evening and later I will post my progress 🙂

[Margrett]

I changed the dataset which contains condtitions. I removed the word "when" and round brackets. So now I have something like this (see attachments). My code is working if and only I put conditions by hand. The problem are the apostrophes ( ' ) in multiple obeservations like 'Fenitures','Radio-TV','Computers' array thinks that there are 3 objects instead of 1. I tried to change the apostrophes ( ' ) to ( " ), to somehow mark out these multiple observations, but I can't get any succes 😞

Could you give me some advice? 

My code now.

PROC surveyselect data=PROJEKT.vin
out=vin_sample
method=srs sampsize=3000; /* simple random sample - srs */
run;

proc sort data=PROJEKT.PODZIALY_ZNAKOWE out=PROJEKT.PODZIALY_ZNAKOWE;
by zmienna war;
quit;

/* zmienna - variable's name */
Proc sql noprint;
Select zmienna into :zm separated by ' '
from PROJEKT.PODZIALY_ZNAKOWE order by zmienna;
quit;

%put &zm.;
%let ilosc=&sqlobs;
%put &ilosc.;

/* warunek - condition */
Proc sql noprint;
Select war into :war separated by ' '
from PROJEKT.PODZIALY_ZNAKOWE order by zmienna;
quit;

%put &war.;

/* grupa - group */
Proc sql noprint;
Select cats("'",grp,"'")
into :grp separated by ' '
from grp_char order by zmienna;
quit;

%put &grp.;

/* %let war='Empty' 'Fenitures, Radio-TV, Computers' 'DiY' 'Owner' 'No'; */    /*it's working for the first five observations if I put it by myself and then the code leaves out the 'Fenitures, Radio-TV, Computers' expression because it's wrong (missing apostrophes inside ) */

%put &war.; 

data vin_grp(drop=i);
          set vin_sample;
                     array zmienna (&sqlobs) &zm.;
                     array warunek (5) $100 _temporary_ (&war.);
                     array grupa (&sqlobs) $1 _temporary_ (&grp.);

          do i = 1 to 5;

                     select (zmienna(i));
                           when (warunek(i)) zmienna(i)=grupa(i);

                     otherwise put 'PROBLEM OBSERVATION';
                     end;
          end;
run;

EDIT: Could I do something like to add additional apostrophes (diffrent from original one) for all of the conditions and then after read by variable all of the conditions and know the exact number of it reduce these apostrophes somewhere in the statement or before (?) --> when (warunek(i)) zmienna(i)=grupa(i);  Is it possible to do that kind of trick?

[Astounding]

When you first posted your original question, you had that part right.  Use a single set of quotes:

 'Fenitures,Radio-TV,Computers'

Whatever it takes to make that happen, do that before the final DATA step.

*********** EDITED:

Your claims about "seriously, all I need ..." might be right, or might be wrong.  That claim is not substantiated by what you have posted so far.  Your final DATA step is small enough that you should be able to test it with absolutely zero macro language to demonstrate what the final program needs to look like.  Then we can worry about how to get macro language to generate that program.

[Margrett]

Okay... But I need the form  'Fenitures','Radio-TV','Computers' at the end. Do you have different idea for this problem?

[Margrett]

Seriously I just need a function to replace apostrophe ( " ) from each value from array "warunek" (condition) in this place.

 EDIT: Oh, I find it. I write this line of code: warunek(i)=tranwrd(warunek(i), '"', '');

And it works fine, but something else in my code are still wrong. Cause zmienna(i) - variable's name - now have the names from conditions array. I don't get it ...

PROC surveyselect data=PROJEKT.vin
out=vin_sample
method=srs sampsize=3000; /* simple random sample - srs */
run;

proc sort data=PROJEKT.PODZIALY_ZNAKOWE out=PROJEKT.PODZIALY_ZNAKOWE;
by zmienna war;
quit;

/* zmienna */
Proc sql noprint;
Select zmienna into :zm separated by ' '
from PROJEKT.PODZIALY_ZNAKOWE order by zmienna;
quit;

%put &zm.;
%let ilosc=&sqlobs;
%put &ilosc.;

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

%put &war.;

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

%put &grp.;

data vin_grp(drop=i);
               set vin_sample;
                           array zmienna (&sqlobs) &zm.;
                           array warunek (&sqlobs) $100 _temporary_ (&war.);
                           array grupa (&sqlobs) $1 _temporary_ (&grp.);

                          do i = 1 to &sqlobs;

                                       warunek(i)=tranwrd(warunek(i), '"', '');
                                       put warunek(i);
                                       put zmienna(i);
                                       put grupa(i);
 
                                      select (zmienna(i));
                                                 when (warunek(i)) zmienna(i)=grupa(i);

                                      otherwise put 'PROBLEM OBSERVATION';
                                      end;
                          end;
run;

[Tom]

You really need to start with an explanation of what your overall goal is. Then you can mention the specific coding issue you are having. Also you really need to post sample data.  Doesn't have to be real data just so it shows the issue you are having.

First thing is to simplify your program to make it easier to read and understand. First you are taking three variables from a dataset and turning them into  three macro variables with space delimited lists of values.

proc sql noprint;
  select zmienna
       , quote(trim(war))
       , quote(trim(grp))
    into :zm separated by ' '
       , :war separated by ' '
       , :grp separated by ' '
 from PROJEKT.PODZIALY_ZNAKOWE
 order by zmienna;
quit;

The first one just has the values and the second two lists have the quoted values.

You then use those to define some arrays.  Not sure why you need the arrays, but let's look at the code you used to define them.

  array zmienna (&sqlobs) &zm.;
  array warunek (&sqlobs) $100 _temporary_ (&war.);
  array grupa (&sqlobs) $1 _temporary_ (&grp.);

So from this we can see that the values pulled from the variable ZMIENNA are being used as the names of the variables that you want to reference using the ZMIENNA array.  The other two lists are being used to populate temporary arrays.

Looking at the rest of the code it is impossible to understand what you want to do. 

  do i = 1 to &sqlobs;
    select (zmienna(i));
      when (warunek(i)) zmienna(i)=grupa(i);
      otherwise put 'PROBLEM OBSERVATION';
    end;
  end;

You are looping over the variables you defined in ZMIENNA array, testing the value (exactly how or why is not clear) and then conditionally either changing the value or writing a note to the SAS log.

Since you are change the values to values from the GRP variable it looks like you are perhaps trying to recode the original values? For this to work the variables referenced by the ZMIENNA array will need to character variables since you have defined the GRUPA array as being of length $1.

Are you just trying to implement a FORMAT?  If so then why do you have only one code value per variable?  And since it looks like the new value represents a GROUP I would example multiple values of WAR to map to the same value of GRP so that it becomes a group variable.  But then what is the meaning of the variable ZMIENNA in the PROJEKT.PODZIALY_ZNAKOWE dataset?  And does that dataset actually have multiple observations per value of ZMIENNA?  Do you actually you have a dataset that looks something like this:

data lookups;
  length varname $32 value $32 group $1 ;
  input varname value group ;
cards;
GENDER MALE M
GENDER FEMALE F
;

 And you want to recode the varaible GENDER so that 'MALE' become 'F' and 'FEMALE' become 'F'?

[Margrett]

I'm sorry maybe it's because of language barrier but sometimes it's dificult to understand the whole meaning of your messages and also It's hard to write to us what's in my mind... I'm from Europe and english is my second language... 😞

I just wanted to say that in select when statement is this construction 

select

when ('something', 'something2', ... , 'somethingn')

And in my dataset there are no observation like this:  'Fenitures,Radio-TV,Computers' , but there are three diferent observations like 

 'Fenitures' or 'Radio-TV' or 'Computers'. Maybe it's more clear now? What I meant .

I want something like this at the end.

PROC surveyselect data=PROJEKT.vin
out=PROJEKT.vin_sample
method=srs sampsize=3000; /* simple random sample - srs */
run;

data vin_grp(drop=i);
set PROJEKT.vin_sample;

select (app_char_branch);
when ('DiY') app_char_branch='3';
when ('Computers') app_char_branch='2';
when ('Fenitures','Radio-TV','Computers') app_char_branch='1';
when ('Empty') app_char_branch = '4';

otherwise put 'PROBLEM OBSERVATION';
end;

select (app_char_cars);
when ('No') app_char_cars='1';
when ('Owner') app_char_cars='2';

otherwise put 'PROBLEM OBSERVATION';
end;

run;

[Astounding]

That can be changed ... but it introduces more problems.  The number of elements in the array changes.  That's why I want to see what the working version of the DATA step looks like with everything hard-coded and no reference anywhere to any macro variables.

[Margrett]

I want something like this.
 

data vin_grp(drop=i);
set PROJEKT.vin_sample;

select (app_char_branch);
when ('DiY') app_char_branch='3';
when ('Computers') app_char_branch='2';
when ('Fenitures','Radio-TV','Computers') app_char_branch='1';
when ('Empty') app_char_branch = '4';

otherwise put 'PROBLEM OBSERVATION';
end;

select (app_char_cars);
when ('No') app_char_cars='1';
when ('Owner') app_char_cars='2';

otherwise put 'PROBLEM OBSERVATION';
end;

run;

[Tom]

That type of wall paper code is easier to produce using a data step instead of macro coding.

First let's turn your example into metadata.

data recodes ;
  infile cards truncover ;
  input varname :$32. code $ value $100.;
cards;
app_char_branch 1 Fenitures
app_char_branch 1 Radio-TV
app_char_branch 2 Computers
app_char_branch 3 DiY
app_char_branch 4 Empty
app_char_cars 1 No
app_char_cars 2 Owner
;

Now let's write a data step that generates the SELECT statements to a text file.

filename code temp ;
data _null_;
  set recodes ;
  by varname code ;
  file code ;
  if first.varname then put 'select (' varname ');' ;
  if first.code then put '  when (' @;
  else put ',' @ ;
  put value :$quote. @ ;
  if last.code then put ') ' varname '=' code :$quote. ';' ;
  if last.varname then put
     '  otherwise put "PROBLEM OBSERVATION " _n_= ' varname '=;'
   / 'end;' 
  ;
run;

So for our sample data that generates these lines of code:

select (app_char_branch );
  when ("Fenitures" ,"Radio-TV" ) app_char_branch ="1" ;
  when ("Computers" ) app_char_branch ="2" ;
  when ("DiY" ) app_char_branch ="3" ;
  when ("Empty" ) app_char_branch ="4" ;
  otherwise put "PROBLEM OBSERVATION " _n_= app_char_branch =;
end;
select (app_char_cars );
  when ("No" ) app_char_cars ="1" ;
  when ("Owner" ) app_char_cars ="2" ;
  otherwise put "PROBLEM OBSERVATION " _n_= app_char_cars =;
end;

Then you can use %INCLUDE to add those lines of code to a data step.

data vin_grp ;
  set PROJEKT.vin_sample;
%include code / source2 ;
run;

[Margrett]

Tom thank you for your code. I didn't write it before but I have to use 'select when' construction. It's for my SAS classes and I don't have a choice.

[Tom]

---

@Margrett wrote:
Tom thank you for your code. I didn't write it before but I have to use 'select when' construction. It's for my SAS classes and I don't have a choice.

---

So use the data step to write the select statement for you. You are still using SELECT statement and it will show that you understand how the SELECT statement works.

The problem with you previous attempt is that there was only one WHEN() clause in the select code. There is no way to introduce a data step loop construct into the middle of the SELECT/WHEN/OTHERWISE/END block of code.  You need to use some type of code generation to create the multiple WHEN() statements.

[Astounding]

Tom's suggestions are good ones.  However ...

If you want to pursue your original path, you should probably adjust the data before extracting anything with SQL.  For example:

data expanded;

set PROJEKT.PODZIALY_ZNAKOWE;

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

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

   output;

end;

drop war k;

rename war_expanded=war;

run;

Then run the SQL code, extracting from the EXPANDED data set.  This separates out the multiple items into separate observations, while keeping the matching variables.

[Margrett]

Okay, but this code of mine also working... Or It will be problem later with it? I mean that part

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

%put &war.;

I add extra apostrophes to keep multiple observations together. proc sql have the right number of observations. And later I reduce this double apostrophes.

data vin_grp(drop=i);
set vin_sample;

array warunek (&sqlobs) $100 _temporary_ (&war.);
array grupa (&sqlobs) $1 _temporary_ (&grp.);
array zmienna (&sqlobs) &zm.;

do i = 1 to &sqlobs;

warunek(i)=tranwrd(warunek(i), '"', '');                      /* HERE I come back to the original text */
put warunek(i)                                                          /* everything is fine in logs */

select (zmienna(i));
when (warunek(i)) zmienna(i)=grupa(i);

otherwise put 'PROBLEM OBSERVATION';
end;
end;
run;

But after this change I have problems with array zmienna and I don't know why... The two rest arrays keep good observations. But array "zmienna" doesn't have values from variable &zm. ... Why? 😞

[Margrett]

I changed my code and I did like you said. But still get the same problem with array called "zmienna" as in my code in earlier post. What's going on..?? 😞

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.20 seconds

cpu time 0.11 seconds

66

67 data expanded;

68 set PROJEKT.PODZIALY_ZNAKOWE;

69

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

71

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

73

74 output;

75

76 end;

77

78 drop war k;

79

80 rename war_expanded=war;

81

82 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.01 seconds

cpu time 0.00 seconds

83

84

85 /* sortuje tabele po zmiennych i warunkach */

86 proc sort data=expanded out=expanded;

87 by zmienna war;

88 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.05 seconds

cpu time 0.00 seconds

89

90

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

92

93 /* zmienna */

94 Proc sql noprint;

95 Select zmienna into :zm separated by ' '

96 from expanded order by zmienna;

97 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

98

99 %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

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

101 %put &ilosc.;

21

102

103 /* warunek */

104 Proc sql noprint;

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

106 Select war into :war separated by ' '

107 from expanded order by zmienna;

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

108 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

109

110 %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'

111

112 /* grupa */

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;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

118

119 %put &grp.;

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

120

121

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

123 data vin_grp(drop=i);

124 set vin_sample;

125

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

127 /* array zmienna (5) app_char_branch app_char_branch app_char_branch app_char_cars app_char_cars; */

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

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

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

WARNING: Partial value initialization of the array grupa.

131

132 put zmienna(1);

133 put zmienna(5);

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

135 do i = 1 to 5;

136

137 /* przy warunkach dodalam podwojne uszy ("), zeby zlaczyc

138 wiele obserwacji, teraz musze je usunac */

139 /* warunek(i)=tranwrd(warunek(i), '"', ''); */

140

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

142 put warunek(i);

143 put zmienna(i);

144 put grupa(i);

145

146

147 select (zmienna(i));

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

149

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

151 otherwise put 'PROBLEM OBSERVATION';

152 end;

153 end;

154 run;

Empty

Empty

Computers

Empty

2

PROBLEM OBSERVATION

DiY

Empty

3

PROBLEM OBSERVATION

Empty

Empty

1

Fenitures

1

1

PROBLEM OBSERVATION

Radio-TV

1

2

(...)