BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DeclanBall
Fluorite | Level 6

Hi all,

 

Please excuse me if I don't explain this very well, I will do my best to outline what I'm after.

I have 2 datasets in SAS that look like this:

 

1)

 pic1.PNG

2)

 PIC2.PNG

I would like a final dataset that takes dataset 2 and assigns it a band based on the min-max spread depicted by dataset 1, something that looks like the following:

 

pic3.PNG

Any help is much appreciated! I had thought about somehow forcing a one to many merge, where every business in dataset 2 was duplicated by the amount of bands, then simply running if minvalue<value<=maxvalue then dummyvariable="YES", then getting rid of any rows that aren't "YES".

 

Many thanks,

Declan

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

There are a few approaches ... here's just one.

 

The first data set is easy to transform into a format:

 

data form;

set dataset1;

fmtname = 'bandcat';

rename band=label min_value=start max_value=end;

run;

proc format cntlin=form;

run;

 

Once you have the format, you can apply it anywhere a format is legal.  For example:

 

data want;

   set dataset2;

   if (0 <= value <= 900) then band = put(value, bandcat.);

run;

 

That would create BAND as a character variable.  If you want it as numeric, it takes a slightly more complex formula:

 

if (0 <= value <= 900) then band = input(put(value, bandcat.), 1.);

View solution in original post

5 REPLIES 5
Astounding
PROC Star

There are a few approaches ... here's just one.

 

The first data set is easy to transform into a format:

 

data form;

set dataset1;

fmtname = 'bandcat';

rename band=label min_value=start max_value=end;

run;

proc format cntlin=form;

run;

 

Once you have the format, you can apply it anywhere a format is legal.  For example:

 

data want;

   set dataset2;

   if (0 <= value <= 900) then band = put(value, bandcat.);

run;

 

That would create BAND as a character variable.  If you want it as numeric, it takes a slightly more complex formula:

 

if (0 <= value <= 900) then band = input(put(value, bandcat.), 1.);

DeclanBall
Fluorite | Level 6
this worked like magic! Never thought to use formats in that way, many thanks
novinosrin
Tourmaline | Level 20

@DeclanBall if it is sets of 100's, math is fun:

 

data have;
input business $ value;
datalines;
A 50
B 70
C 360
D 780
E 356
F 126
G 800
H 200
I 150
;


data want;
set have;
if mod(value,100)=0 then Band=int(divide(value,100));
else Band=int(divide(value,100))+1;
run;
DeclanBall
Fluorite | Level 6
They unfortunately weren't equal increments, apologies for the example dataset being misleading, thank you for your help though
SuryaKiran
Meteorite | Level 14

Try PROC SQL Cartesian product with where clause to exclude the records you don't need.


DATA value;
input band min max;
datalines ;
1 0 100
2 101 200
3 201 300
4 301 400
5 401 501
6 501 600
7 601 700
8 701 800
9 801 900
;
DATA HAVE;
INPUT Business $ value;
datalines ;
A 50
B 70
C 360
D 780
E 356
F 126
G 800
H 200
I 150
;
RUN;

PROC SQL;
CREATE TABLE WANT AS
SELECT Business,Value,Band
FROM HAVE,VALUE
WHERE VALUE>MIN AND VALUE<=MAX;
QUIT;
Thanks,
Suryakiran

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
  • 5 replies
  • 749 views
  • 3 likes
  • 4 in conversation