@alepage wrote:
Hello,
I agree with you, putting a list of values into a macro variable may not be in certain circumstance, the best thing to do.
I have read your message attentively.
What I have done its to put my code in a dataset as recommended. At the end, I have two different datasets, one containing the numeric code and another one containing the alphanumeric code. Ex;
Table1
10010
10020
And so on.
Table2
Ved10010
Ved10020
And so on
Thereafter, I have use proc transpose (out = Liste) with table2 which permit me to obtain the following dataset
Ved10010 Ved10020 … and so on.
Table3 contains the variables unite from Unite1 – Unite13;
I have made a merge of table3 and Liste to get a dataset (Table4) as below:
Unite1 Unite2 Unite3 …Unite13 ved10010 ved10020…Ind80800 and so so.
Thereafter, I would like to initialize all the variable starting by ved or ind to zero.
Then, what I would like to do its to check for each observation of dataset Table4 if the values for the variable Unite1 to Unite13 can be found in values of table1 and if so the variable ved or ind would have the value of one.
Ex;
If the value of 10010 is found in one of the value Unites1 to Unite13 then ind10010 will have the value of one otherwise, ind10010 is zero.
I have made few tests with the arrays and I am losing the original value of Unite1 to Unite13 (reset to zero) and I am not able to assign a value of one or zero to ind10010 and so on.
What’s wrong with my arrays??
Regards,
Alain.
That sounds even worse than the original approach. Instead make everything vertical. Then summarize and transpose if you want.
So the data you want to search looks something like this. So instead of columns named UNITE1 to UNITEn you have up to N rows with two columns one with the value, call it UNITE, and one with the index, call it ROW.
data facts ;
input id row unite ;
cards;
1 1 101
1 2 102
2 1 201
2 2 101
2 3 102
;
Then the data you want to search for looks something like this.
data search ;
input prefix $ value ;
cards;
ved 101
ved 102
ved 103
ved 201
;
Now combine them an aggregate to the ID* search term level. Also generate a dummy set of zeros for the ones that are not found and re-merge to two sets. You can then transpose back to your wide format if you need it for some other purpose.
proc sql ;
create table matches as
select a.id,b.prefix,b.value
, max(case when b.value = a.unite then 1 else 0 end) as found
from facts a inner join search b
on a.unite = b.value
group by 1,2,3
order by 1,2,3
;
create table dummy as
select a.id,b.prefix,b.value,0 as found
from (select distinct id from facts) a
full join search b
on 1=1
order by 1,2,3
;
quit;
data want ;
merge dummy matches ;
by id prefix value ;
run;
proc print;
run;
proc transpose data=want out=report ;
by id ;
id prefix value ;
var found ;
run;
proc print;
run;
Obs id prefix value found
1 1 ved 101 1
2 1 ved 102 1
3 1 ved 103 0
4 1 ved 201 0
5 2 ved 101 1
6 2 ved 102 1
7 2 ved 103 0
8 2 ved 201 1
Obs id _NAME_ ved101 ved102 ved103 ved201
1 1 found 1 1 0 0
2 2 found 1 1 0 1
... View more
