SELECT WHEN statement using arrays and variables

[Margrett]

Hi there!

I have a problem using "Select when" statement. I have conditions, values and names in three arrays. I want to change my dataset (here are only sample) automaticaly although there are no errors or warnings I still don't get results. Nothing change in main dataset. I have information about "PROBLEM OBSERVATION" in my log but I don't know why. This is my code.

%let zm='APP_CHAR_BRANCH' 'APP_CHAR_BRANCH' 'APP_CHAR_BRANCH' 'APP_CHAR_CARS' 'APP_CHAR_CARS';
%let war='Empty' 'Fenitures, Radio-TV, Computers' 'DiY' 'Owner' 'No';
%let grp='3' '1' '2' '2' '1';

/* the "zm" and "war" variables are the exact text from observations from the main datasets ("zm" are variables names and "war" are the values in observations */

data vin_grp(drop=i);
        set vin;
                   array zmienna (5) $15 zm1-zm5 (&zm.);
                   array warunek (5) $100 war1-war5 (&war.);
                   array grupa (5) $1 grp1-grp5 (&grp.);

                  do i = 1 to 5;

                          put warunek(i);       /*I added this to see which observations has problems but It wasn't helpfull from me 😞 */
                          put zmienna(i);
                          put grupa(i);

                          select (zmienna(i));
                                    when (warunek(i)) zmienna(i)=grupa(i);                 /* for example: when ('DiY') app_char_branch='2'; */

                          otherwise put 'PROBLEM OBSERVATION';
                          end;
                 end;

run;

Could you help me somehow?

M.M.

PS: Main data is in the attachments

[Astounding]

I believe you are using SELECT incorrectly.  The code you have is looking for observations that have:

zmienna(i) = warunek(i)

So these never match, and everything is a PROBLEM OBSERVATION.

What condition are you searching for in the SELECT statement?  You don't  have to code it ... just describe it.

[Margrett]

I need to change all of the observations in vin dataset from all of the variable starting with "app_". I've done a categorization and want to change these text inside to number (group after cateforization :1,2 or 3).

I have table PODZIALY_ZNAKOWE where are variable name (zmienna), condition (war) and group (grp). On that base I want to change my datasets "vin". So for example I don't want to have anymore for variable app_char_branch observations like "Empty" but "2". Instead of "DiY" - "3" etc.

[Reeza]

Can you try a format instead? It seems like it would be more intuitive and easier to follow. And it works better if you need to repeat this multiple times. 

See the first example here:

http://www2.sas.com/proceedings/sugi30/001-30.pdf

[Margrett]

But I have to work with that data later on using proc corr and do some analysis. So I guess format wan't be a good solution. And I have 200 more numeric variables to do the same with... 😞

[Reeza]

That's exactly why you would use a format, it scales better than a whole lot of SELECT statements and is reusable. You still convert your data but it's much much easier. And you can create formats from data sets, so if you have a lookup table that maps a value and the corresponding new value you can easily create your formats without having to manually type each one out. No macro's and easier. 

In a perfect world you'd have a lookup table structured like the following, which I suspect you may:

VariableName OldValue NewValue

[Astounding]

If the "zm" are actually variable names, this would be the way to match them:

%let  zm=APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_CARS APP_CHAR_CARS;

Then later:

array zmienna (5) &zm;

It's unusual to include the same variable multiple times within an array, but it should work in this case.

[Margrett]

Instead of "array zmienna (5) $15 zm1-zm5 (&zm.);" ?

Nope it doesn't work.

PS: When I write a line like this: 

when ('DiY') app_char_branch='2';

without using variables I got the results I needed, but of course I don't want to write these all conditions by myself...

[Astounding]

Yes, that's the right thing to do.  You might have to post the log so we can see the part that isn't working.

[Margrett]

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

cpu time 0.31 seconds

66

67 %let zm='APP_CHAR_BRANCH' 'APP_CHAR_BRANCH' 'APP_CHAR_BRANCH' 'APP_CHAR_CARS' 'APP_CHAR_CARS';

68

69 /* %let war="when ('Empty')" "when ('Fenitures','Radio-TV','Computers')" "when ('DiY')" "when ('Owner')" "when ('No')";

69 ! */

70 %let war='Empty' 'Fenitures, Radio-TV, Computers' 'DiY' 'Owner' 'No';

71 /* */

72 /* %put &war.; */

73 %let grp='3' '1' '2' '2' '1';

74 /* %let grp=3 1 2 2 1; */

75 /* %put &grp.; */

76

77 data vin_grp(drop=i);

78 set vin_sample;

79 array zmienna (5) &zm.;

_

79

ERROR 79-322: Expecting a ).

NOTE: Line generated by the macro variable "ZM".

79 'APP_CHAR_BRANCH' 'APP_CHAR_BRANCH' 'APP_CHAR_BRANCH' 'APP_CHAR_CARS' 'APP_CHAR_CARS'

_________________

79

ERROR 79-322: Expecting a (.

80 array warunek (5) $100 war1-war5 (&war.);

ERROR: Attempt to initialize variable zmienna1 in numeric array zmienna with character constant 'APP_CHAR_BRANCH'.

ERROR: Attempt to initialize variable zmienna2 in numeric array zmienna with character constant 'APP_CHAR_BRANCH'.

ERROR: Attempt to initialize variable zmienna3 in numeric array zmienna with character constant 'APP_CHAR_BRANCH'.

ERROR: Attempt to initialize variable zmienna4 in numeric array zmienna with character constant 'APP_CHAR_CARS'.

ERROR: Attempt to initialize variable zmienna5 in numeric array zmienna with character constant 'APP_CHAR_CARS'.

81 array grupa (5) $1 grp1-grp5 (&grp.);

82

83 do i = 1 to 5;

84 put warunek(i);

85 put zmienna(i);

86 put grupa(i);

87 select (zmienna(i));

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

89

90 /* when ('DiY') app_char_branch='2'; */

91 /* when ('Fenitures', 'Radio-TV', 'Computers') app_char_branch='1'; */

92 /* when ('Empty') app_char_branch='3'; */

93

94 otherwise put 'PROBLEM OBSERVATION';

95 end;

96 end;

97 run;

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

88:9 88:21

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.VIN_GRP may be incomplete. When this step was stopped there were 0 observations and 24 variables.

WARNING: Zbiór WORK.VIN_GRP nie został zastąpiony, ponieważ this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

98

99

100 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

113

[Astounding]

Take a look at the statement that I posted earlier:

%let zm= ......;

All the quotes should be removed.  SAS uses quotes to indicate text, but removes the quotes when referring to variable names.

[Margrett]

Still no. 😞 I've tried these. To add " ' " or no etc. 

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

61

62

63 %let zm=APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_CARS APP_CHAR_CARS;

64

65 /* %let war="when ('Empty')" "when ('Fenitures','Radio-TV','Computers')" "when ('DiY')" "when ('Owner')" "when ('No')";

65 ! */

66 %let war='Empty' 'Fenitures, Radio-TV, Computers' 'DiY' 'Owner' 'No';

67 /* */

68 /* %put &war.; */

69 %let grp='3' '1' '2' '2' '1';

70 /* %let grp=3 1 2 2 1; */

71 /* %put &grp.; */

72

73 data vin_grp(drop=i);

74 set vin_sample;

75 /* array zmienna (5) $15 zm1-zm5 (&zm.); */

76 array zmienna (5) $ (&zm.);

NOTE: Line generated by the macro variable "ZM".

76 APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_CARS APP_CHAR_CARS

_______________

22

76

ERROR 22-322: Syntax error, expecting one of the following: łańcucha znaków w cudzysłowie, stałej numerycznej,

stałej daty-czasu, braku danych, iterator, (.

ERROR 76-322: Syntax error, statement will be ignored.

77 array warunek (5) $100 war1-war5 (&war.);

78 array grupa (5) $1 grp1-grp5 (&grp.);

79

80 /* do i = 1 to 3; */

81 /* */

82 /* put warunek(i); */

83 /* put zmienna(i); */

84 /* put grupa(i); */

85 /* put warunek(i); put zmienna(i); put grupa(i); */

86 select (zmienna(1));

87 /* when (warunek(i)) zmienna(i)=grupa(i); */

88 when (warunek(1)) zmienna(1)='1';

89

90 /* when ('DiY') app_char_branch='2'; */

91 /* when ('Fenitures', 'Radio-TV', 'Computers') app_char_branch='1'; */

92 /* when ('Empty') app_char_branch='3'; */

93

94 otherwise put 'PROBLEM OBSERVATION';

95 end;

96 /* end; */

97 run;

WARNING: The variable i in the DROP, KEEP, or RENAME list has never been referenced.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.VIN_GRP may be incomplete. When this step was stopped there were 0 observations and 24 variables.

WARNING: Zbiór WORK.VIN_GRP nie został zastąpiony, ponieważ this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

98

99

100

101 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

114

[ballardw]

Note that @Astounding recommended:

array zmienna (5) &zm;

You used

array zmienna (5) $ (&zm.);

Parenthesized values in that location are not variable names but values to assign to the variables created

[Margrett]

Oh! My bad. I changed the code several times so... My mistake! It's working! Thank you! 🙂 

I have external question now :)) 

I want to create three variables automaticaly from that second dataset. Something like that.

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

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

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

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

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

%put &war.;

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

%put &grp.;

/* %let zm=APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_CARS APP_CHAR_CARS; */
/* */
/* %let war="when ('Empty')" "when ('Fenitures','Radio-TV','Computers')" "when ('DiY')" "when ('Owner')" "when ('No')"; */
/* %let war='Empty' 'Fenitures, Radio-TV, Computers' 'DiY' 'Owner' 'No'; */
/* */
/* %put &war.; */
/* %let grp='3' '1' '2' '2' '1'; */
/* %let grp=3 1 2 2 1; */
/* %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;

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

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

But then I will have new problems. I need to erase word "When" And "(" , ")" from condition (war) and also put more " ' " when there are more then one condition. Secondly in group (grp) the type of that column is numeric. And I will need character. Could you help me with that also?

I thougth that I use the condition variable (war) in a different way, easier, but I can't do that... I mean instead of

when (warunek(i)) zmienna(i)=grupa(i); (in this part I type variable warunek by hand so I overlooked the word "when" etc.)

do

warunek(i) zmienna(i)= grupa(i); (in this case I will take the whole text from variable but I get an error according to missing when word in select statement).

[Astounding]

Advice:  get this code to work without macro language at all.  Once you have that done, you can consider how to apply macro language.  There are too many features about the program that are either not working, or are working poorly.  

Macro language does not add any functionality to a SAS program.  It merely automates a program that is already working.  But you have not gotten to the point of having a working program yet.