DATA Step, Macro, Functions and more

compare the two datssets using sql

Reply
Contributor
Posts: 32

compare the two datssets using sql

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

Regular Contributor
Posts: 233

compare the two datssets using sql

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;

Super User
Posts: 9,687

compare the two datssets using sql

What is the value of this macro variable ?

Contributor
Posts: 32

compare the two datssets using sql

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

Contributor
Posts: 32

compare the two datssets using sql

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

New Contributor
Posts: 3

compare the two datssets using sql

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

Super User
Posts: 9,687

compare the two datssets using sql

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

Ask a Question
Discussion stats
  • 6 replies
  • 356 views
  • 3 likes
  • 4 in conversation