topic compare the two datssets using sql in SAS Programming

compare the two datssets using sql

shru — Fri, 30 Mar 2012 02:59:39 GMT

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

compare the two datssets using sql

Hima — Fri, 30 Mar 2012 03:14:11 GMT

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;

compare the two datssets using sql

Ksharp — Fri, 30 Mar 2012 06:07:56 GMT

What is the value of this macro variable ?

compare the two datssets using sql

shru — Fri, 30 Mar 2012 06:40:02 GMT

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

compare the two datssets using sql

shru — Fri, 30 Mar 2012 06:51:16 GMT

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

compare the two datssets using sql

Ksharp — Fri, 30 Mar 2012 07:17:20 GMT

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

compare the two datssets using sql

shshiva — Fri, 30 Mar 2012 08:43:30 GMT

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