BookmarkSubscribeRSS Feed
shru
Calcite | Level 5

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

6 REPLIES 6
Hima
Obsidian | Level 7

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
Super User

What is the value of this macro variable ?

shru
Calcite | Level 5

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
Calcite | Level 5

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
Calcite | Level 5

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1069 views
  • 3 likes
  • 4 in conversation