compare the two datssets using sql

[shru]

Hi,

My first datset is like,

Name

ACU 

BDU 

POI 

and my second dataset is like

Name    value     volume

ACU      1               23

BDU    4               78

HYY    8               91

QWE   3               75

POI     1               89

PFD    3               56

KHT    1               02

i.e my first dataset is a subset of second,

Now i need to match the names from both the datasets and put them in a macro variable using proc sql.

How can i achieve this?

Need help ASAP

Thnaks

[Hima]

DATA HAVE;
INPUT NAME $  VALUE     VOLUME ;
CARDS;
ACU      1               23
BDU    4               78
HYY    8               91
QWE   3               75
POI     1               89
PFD    3               56
KHT    1               02
;
RUN;

DATA WANT;
INPUT NAME $  VALUE     VOLUME ;
CARDS;
ACU      1               23
BDU    4               78
HYY    8               91
;
RUN;

PROC SQL NOPRINT;
SELECT NAME INTO :TEST SEPARATED BY ' ' FROM HAVE WHERE NAME NOT IN (SELECT NAME FROM WANT);
QUIT;

%PUT &TEST;

[Ksharp]

What is the value of this macro variable ?

[shru]

Hi ksharp,

the value of the macro variable TEST  will be   :ACU,BDU,HYY

The code workrd fine,

help me in using this macro,i need to put the values in the excel sheet for the values of macro variable.

How can i do this?

Thanks

[shru]

Hi Ksharp,

One more help,

Now i will get the common values from the your code.

Can i get the for single single  value i.e i need all the values in a seperate macro variable along with the value

and if the name and value does not exits then i need to put zero.

Its like i will compare both datsets,if values are there then put the values else put zero.

Need ASAP

Thanks

[shshiva]

Hi,

Try this...

PROC SQL ;

SELECT count(name) INTO :count FROM HAVE WHERE NAME IN (SELECT NAME FROM WANT);

SELECT NAME INTO :test1- :test%left(&count) FROM HAVE WHERE NAME IN (SELECT NAME FROM WANT);

QUIT;

%PUT &test1 &test2 &test3  &count;

Thanks,

Shiva

[Ksharp]

I still don't know where HYY is coming from ?