topic Re: SELECT WHEN statement using arrays and variables in SAS Studio

SELECT WHEN statement using arrays and variables

Margrett — Mon, 22 Jan 2018 21:16:49 GMT

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

Re: SELECT WHEN statement using arrays and variables

Astounding — Mon, 22 Jan 2018 21:22:30 GMT

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.

Re: SELECT WHEN statement using arrays and variables

Margrett — Mon, 22 Jan 2018 21:32:01 GMT

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.

Re: SELECT WHEN statement using arrays and variables

Reeza — Mon, 22 Jan 2018 21:35:46 GMT

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

Re: SELECT WHEN statement using arrays and variables

Astounding — Mon, 22 Jan 2018 21:35:54 GMT

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.

Re: SELECT WHEN statement using arrays and variables

Margrett — Mon, 22 Jan 2018 21:50:41 GMT

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... 😞

Re: SELECT WHEN statement using arrays and variables

Margrett — Mon, 22 Jan 2018 21:55:59 GMT

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

Re: SELECT WHEN statement using arrays and variables

Astounding — Mon, 22 Jan 2018 21:56:10 GMT

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.

Re: SELECT WHEN statement using arrays and variables

Margrett — Mon, 22 Jan 2018 21:59:33 GMT

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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

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

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.; */

77 data vin_grp(drop=i);

78 set vin_sample;

79 array zmienna (5) &zm.;

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'

_________________

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.);

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);

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

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

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

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

100 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

113

Re: SELECT WHEN statement using arrays and variables

Astounding — Mon, 22 Jan 2018 22:05:03 GMT

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.

Re: SELECT WHEN statement using arrays and variables

Margrett — Mon, 22 Jan 2018 22:09:39 GMT

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

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

63 %let zm=APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_BRANCH APP_CHAR_CARS APP_CHAR_CARS;

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.; */

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

_______________

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.);

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';

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

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

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

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

100

101 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

114

Re: SELECT WHEN statement using arrays and variables

ballardw — Mon, 22 Jan 2018 22:16:05 GMT

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

Re: SELECT WHEN statement using arrays and variables

Margrett — Mon, 22 Jan 2018 22:35:55 GMT

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.)

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).

Re: SELECT WHEN statement using arrays and variables

Astounding — Mon, 22 Jan 2018 23:34:52 GMT

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.

Re: SELECT WHEN statement using arrays and variables

Reeza — Tue, 23 Jan 2018 00:05:57 GMT

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

Re: SELECT WHEN statement using arrays and variables

Margrett — Tue, 23 Jan 2018 08:25:17 GMT

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

Re: SELECT WHEN statement using arrays and variables

Margrett — Tue, 23 Jan 2018 13:11:52 GMT

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?

Re: SELECT WHEN statement using arrays and variables

Astounding — Tue, 23 Jan 2018 16:25:58 GMT

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.

Re: SELECT WHEN statement using arrays and variables

Margrett — Tue, 23 Jan 2018 15:51:18 GMT

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

Re: SELECT WHEN statement using arrays and variables

Margrett — Tue, 23 Jan 2018 16:36:51 GMT

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;