Problems with PUTVARN() and LOCATEN()

[deleted_user]

first of all: I have never been closer 🙂 thx a lot...

one thing left: I have put a control-print in the do-loop.. like this (btw... sorry I dont know how to post code in Courier font):


.
.
if UPCASE("&&m&i") = "S%" then do;

%put changing &&d&i => &&m&i;

if &&d&i le 10 then &&d&i=1;
else if 10 lt &&d&i le 20 then &&d&i=2;
.
.

the result is the following:
661 %a
changing D_AUFG => DATUM
changing D_VEGB => DATUM
changing D_SCHN_3 => DATUM
changing D_SCHN_4 => DATUM
changing D_SCHN_5 => DATUM
changing D_SCHN_6 => DATUM
changing D_SCHN_7 => DATUM
changing D_SCHN_8 => DATUM
changing DICHVS_9 => BON19
changing DICHVS_10 => BON19
changing DICHVS_11 => BON19
changing DICHVS_12 => BON19
changing DICHVS_13 => BON19
changing DICHVS_14 => BON19
changing DICHVS_15 => BON19
changing DICHNS_16 => BON19
changing DICHNS_17 => BON19
changing DICHNS_18 => BON19
changing DICHNS_19 => BON19
changing DICHNS_20 => BON19
changing DICHNS_21 => BON19
changing DICHNS_22 => BON19
changing DICHVW => BON19
changing DICHNW => BON19
changing WDAVW_25 => BON19
changing WDANW_26 => BON19
changing WDAVS_27 => BON19
changing WDAVS_28 => BON19
changing WDAVS_29 => BON19
changing WDAVS_30 => BON19
12 Das SAS System 23:07 Tuesday, November 16, 2010

changing WDAVS_31 => BON19
changing WDAVS_32 => BON19
.
.
.

am I wrong or does it change even values for DATUM and BON19??? (which it shouldnt do)?

Tom

[deleted_user]

hello SPR,

but seems the %put statement is printed regardless of the result of the if-clause. I am such a newby... my god.

I just added a counter that counts whenever the code enteres the if-clause, and the counter ended up by 18, which is a good value 🙂

thank you a lot for your precious time, you helped me a lot...
and I still have to learn a lot about SAS... well after that project I will never have anything to do with SAS :)... well, never say never.

so thank you again,
Tom

[SPR]

Hello Tom,

Yes, it is necessary to use put instead of %put in this case.

If you do not mind, this is the second version of this code which is close to what you posted originally:
[pre]
proc SQL noprint;
select COUNT(*) into :n from table2;
%let n=%trim(&n);
select method into :m1-:m&n from table2;
quit;
%macro a;
%local i;
data r (drop=i);
set table1;
array d {&n} density1-density&n;
%do i=1 %to &n;
i=&i;
if "&&m&i" = "S%" then do;
if d[i] <= 10 then d[i]=1;
else if 10 < d[i] <= 20 then d[i]=2;
else if 20 < d[i] <= 30 then d[i]=3;
else if 30 < d[i] <= 40 then d[i]=4;
else if 40 < d[i] <= 50 then d[i]=5;
else if 50 < d[i] <= 60 then d[i]=6;
else if 60 < d[i] <= 70 then d[i]=7;
else if 70 < d[i] <= 80 then d[i]=8;
else if 80 < d[i] then d[i]=9;
end;
%end;
run;
%mend;
%a[/pre]
Sincerely,
SPR

[deleted_user]

hello SPR,

this is weird, I wanted to combine your new solution with my macro and wrote this:

/********************************* BEGIN CODE *****************************/

data TransMethods;
set Merk; /* in Merk are the methods listed */
where METHODE="S%";
keep NAME METHODE;
run;

/* delete multiple keys */
proc sort data=TransMethods NODUPKEY;
by NAME;
run;

/* Writing methods to translate into macro variables */
proc SQL noprint;

select COUNT(*) into :nTransNames from TransMethods;
%let nTransNames=%trim(&nTransNames);
select NAME into :strTransName1-:strTransName&nTransNames from TransMethods;

quit;


/* translate S% values into BON19 values */;
%macro CheckValues( strDataSet );
%local j;
data &strDataSet;
set &strDataSet;

array strNamesThis

_numeric_;
nChanged = 0;
nThis = dim(strNamesThis);

/* Checking method */
do iVal=1 to nThis;
%do j=1 %to &nTransNames;
j=&j;
if "&&strTransName&j" = strNamesThis[iVal] then do;
nChanged = nChanged+1;
if strNamesThis[iVal] le 10 then strNamesThis[iVal]=1;
else if 10 lt strNamesThis[iVal] le 20 then strNamesThis[iVal]=2;
else if 20 lt strNamesThis[iVal] le 30 then strNamesThis[iVal]=3;
else if 30 lt strNamesThis[iVal] le 40 then strNamesThis[iVal]=4;
else if 40 lt strNamesThis[iVal] le 50 then strNamesThis[iVal]=5;
else if 50 lt strNamesThis[iVal] le 60 then strNamesThis[iVal]=6;
else if 60 lt strNamesThis[iVal] le 70 then strNamesThis[iVal]=7;
else if 60 lt strNamesThis[iVal] le 80 then strNamesThis[iVal]=8;
else if 80 lt strNamesThis[iVal] then strNamesThis[iVal]=9;
end;
%end;
end;

run;
%mend;

%CheckValues( V_Daten )

/***************************** END CODE ******************************/

in V_Daten is the original data.


now the problem:
it changes ALL variables, not only those whose name are listed strTransName1-strTransNameN

why?

I am desperating with this

[SPR]

Hello Tom,

Your code contains TWO do loops in the last data step but should be one. See my code.

Sincerely,
SPR

[deleted_user]

yes, but the problem is, that table2 CAN contain the data but obligatory... so the code will exit with an error if it cant find the name in the table. thats why I step through the names and check, if the particular name must be translated or not.

or am I wrong?

Tom

[SPR]

Sorry, I do not understand. I think that it is necessary to check the condition:

if "&&strTransName&j" = "S%" then do;

Please, explain.

SPR

[deleted_user]

sorry, I wrote wrong, it must be

table2 CAN contain the data but NOT obligatory.

this means: in table 2 there is a choice of the entries in table1. so we cannot assume, that the size of the array is n (which is the number of objects in table1).

so I decided to create an arry of all those names that must be translated, this is stored in strTransName1 - strTransName2. so I dont need to chekc for S% of the method, because I already filtered only these names whose method is S%.

so all I need now is that my macro checks, if the name of the variable can be found in strTransName1 - strTransNameN.

so I wrote my code and its not doing what I want that it does 😞

Tom

[SPR]

The simpliest way to overcome this is to recreate table2:
[pre]
proc SQL;
/* Number of variables other than Year in table1 */;
select COUNT(*) into :ntable1 from SASHELP.VCOLUMN
where memname="TABLE1" and UPCASE(NAME) NE "YEAR";
%let ntable1=%trim(&nTable1);
/* Create a list of variables other than YEAR from table1 */;
create table names as
select distinct Name from SASHELP.VCOLUMN
where memname="TABLE1" and UPCASE(NAME) NE "YEAR";
/* Merge with table 2 */;
create table table22 as
select a.*, b.methode from names as a left join merk as b
on a.name=b.name;
run;
[/pre]
SPR

[data_null__]

This version is the same as my last version only different. It creates an array from TABLE2 where method eq 'S%' if there are extra variables in TABLE2 that don't exist in TABLE1 it doesn't matter and no new variables are added to TABLE1.


[pre]
data density;
input year density1 density2 density10;
cards;
2004 100 2 56
2005 89 4 78
2006 45 7 98
;;;;
run;
data method;
input name:$32. method:$8.;
retain i 0;
cards;
density1 S%
density2 BON19
density6 S%
density8 S%
density10 S%
;;;;
run;
proc sql noprint;
select name into :toBON separated by ' '
from method where method eq 'S%';
run;
%put NOTE: TOBON=&tobon;

data density;
modify density;
array s

8 &tobon;
do _n_ = 1 to dim(s);
if not missing(s[_n_]) then do;
do b=10 to 80 by 10;
if s[_n_] lt b then do;
s[_n_] = b/10;
goto found;
end;
end;
s[_n_]=9;
found:
end;
end;
replace;
run;
proc print data=density;
run;
[/pre]

[SPR]

Hello Tom,

1) If you need to rewrite simply replace r&i with d&i, e.g.
[pre]
replace if &&d&i <= 10 then r&i=1;
with if &&d&i <= 10 then d&i=1;
[/pre]
2) you are right.

3) plase delete drop=i in my code. it is a leftover from the previous version.

SPR

[data_null__]

I may not understand competly but it sounds like all the information about what needs to be modified can be derived from "table2" the table of names and methods.

From that you can create a data set of variables that need to be modified. This data set can be used in a data step to create an array of the variables that need to be transformed.

[pre]
proc format;
value s2bon
0-<10 = '1'
10-<20 = '2'
20-<30 = '3'
30-<40 = '4'
40-<50 = '5'
50-<60 = '6'
60-<70 = '7'
70-<80 = '8'
80<-high='9'
;
run;

data density;
input year density1 density2 density10;
cards;
2004 100 2 56
2005 89 4 78
2006 45 7 98
;;;;
run;
data method;
input name:$32. method:$8.;
retain i 0;
cards;
density1 S%
density2 BON19
density10 S%
;;;;
run;
proc transpose data=method out=S(drop=_name_ where=(0));
where method eq 'S%';
id name;
var i;
run;
proc contents varnum;
run;
data density;
if 0 then set s;
array s

_numeric_;
modify density;
do _n_ = 1 to dim(s);
s[_n_]=input(put(s[_n_],s2bon.),F1.);
end;
replace;
run;
proc print data=density;
run;

[/pre]

[Patrick]

Hi

It's always hard to post somthing after data _null_; .... but because I've already created the code I don't want to waste it now. So below what I planned to proprose:


As always there are many ways in SAS to get a job done.

Below a bit another approach which might work for you.


/* --------------------- */
/* 1. create sample data */
data have;
input year density1 density2 densityX;
datalines;
2004 100 2 56
2005 89 4 78
2006 45 7 98
;
run;

data Methods;
input name $ method $;
datalines;
density1 S%
density2 BON19
densityX S%
;
run;

/* -------------------- */
/* 2. create format */
proc format;
value PctToMark
low -< 10 =1
10 -< 20 =2
20 -< 30 =3
30 -< 40 =4
40 -< 50 =5
50 -< 60 =6
60 -< 70 =7
70 -< 80 =8
80 - high =9
;
run;

/* ------------------------------------------------------------------*/
/* 3. generate SAS data step code and write it to a temporary file */
filename tmpfile temp;
data _null_;
file tmpfile;
set Methods;
if method='S%' then
do;
put name '=input(put(' name ',PctToMark.),8.);' ;
end;
run;

/* ----------------------------------------------------------*/
/* 4. convert % to marks using SAS code generated in step 3 */
data want;
set have;
%include tmpfile;
run;

proc print data=want;
run;


The important thing in SAS:
SAS executes a program from top to bottom and compiles and executes every run group (what ends with a "run") separately.

That makes it possible in step 3 to use a data _null_ step (_null_: no SAS data set will be created) to generate SAS code (as text) and write this text to a temporary file.

In step 4 this temporary file gets included (using a SAS macro language %include which is kind of pre-processing BEFORE the SAS language interpreter kicks in).
The SAS interpreter therefore will "see" the SAS code which got written to the temporary file in step 3, compile the code in this run group and then execute.

In step 2 a SAS format gets created. SAS formats and informats are used to write and read data (and are a fast way to convert data using kind of a lookup mechanism).


Hope this explanations help. Others might have used more correct terminology (I'm always a bit at war with this).


HTH
Patrick

Message was edited by: Patrick

[deleted_user]

hello _null_ and Patrick,

thank you for your replies... I tried to understand both version, but I failed to understand the version of _null_. its too hard for me, too many new things (format, transpose, contents) where the help system is not really helpful to me...

And to be honest, for a c++-programmer an expression like "if 0 then set s;" cannot make sense, because the boolean expression 0 must be expressed as FALSE always, so the if clause is never executed, it doesnt make sense to me :(. and understanding that weird logical system would take too much energy.

I even fail to understand these small little things, so undestanding even more complex stuff would take too long time for me.

as I only have to do this one thing with SAS I dont want to get too deep into it. So I decided to leave it with the one running version from SPR and be happy with it 🙂

thank you again for your precious time,
Tom

[Patrick]

Hi Tom

I believe a rule of thumb in the SAS world is not to use SAS macro language if it's not necessary.

To combine macro syntax with base SAS syntax is very powerful – but often also much harder to implement and read.

As often data _null_; posted a solution which is just beautiful in its simplicity and effectiveness.

Simple code is often also more robust and easier to adapt to additional requirements.

I therefore would consider switching to data _null_;’s approach.

No offence meant SPR!

HTH
Patrick