BookmarkSubscribeRSS Feed
jen123
Fluorite | Level 6

Attached file is an example (smaller size) of my dataset.

Columns A - E:  Customer ID and address to send the promotion to.

Columns J & K:  Each range set is for a different promotion.  Only the customers in the range are eligible for the promotion.

I would like to create a dataset that, for each given range, SAS will look at the data in columns A-E and produce a table/results of only the customer ids in that range.  Note:  The Customer ID Range List and Customer Information are in two different datasets.

Any help is appreciated!

10 REPLIES 10
Jay_TxOAG
Quartz | Level 8

providing sample data (a data step with datalines) makes answering these types of questions so much easier...then we can just plug it into our SAS editor and fool with it until we get what seems like a solution. Also, we can then provide code that runs without errors...instead of just guessing...

Cynthia_sas
SAS Super FREQ

Hi:

In fact, this suggestion is one of the things I show in the paper that I co-authored with Renee Harper on how to make the best use of the SAS Community Forums. Just posted online:

http://support.sas.com/resources/papers/proceedings12/189-2012.pdf

cynthia

Jay_TxOAG
Quartz | Level 8

that is a very nice reference tool....

jen123
Fluorite | Level 6

This response is for Jay@TxOAG.

I don't know how to take your response.

I posted this because I don't know where to start.  I have not written the program, hence why I didn't include the "data steps with datalines".  I thought if I posted a sample of what my dataset looks like, maybe I can get some suggestions on how to approach the problem.

Linlin
Lapis Lazuli | Level 10

There is no attachment in your original post.

jen123
Fluorite | Level 6

Linlin - Thank you very much for the response.  My apology as I thought I had attached the file in my original post...hence why I wrote "attached file".


To attach a file, i clicked on BROWSE and selected the file.  I am going to try to attach it again...and please let me know if it's not there. 

Linlin
Lapis Lazuli | Level 10

We have your attachment!Smiley Happy

shivas
Pyrite | Level 9

Hi Jen,

Try this...Hope it helps..

data range;

input customer_ID range;

cards;

17092730413  170927208310

;

run;

data fmt (keep=FMTNAME START END LABEL TYPE);

length FMTNAME $30. START END $256.;

set WORK.range;

      FMTNAME = 'Range' ;

      START   = Customer_ID ;

      END     = Range;

      LABEL   = 'Range';

      TYPE    = 'N' ;

run;

proc format cntlin=fmt lib=work; run;

data customer;length x $20.;

input Customer_ID House_No Street_Name $ 25-39 City $ 40-55 State $ 56 -60;

x=put(customer_ID,Range.);

cards;

170927308313 123    Elks Grove    Chicago       IL

171540339727 456    Crab Lake    Baltimore   MD

170060423416 7890 Winery Lane    Frederick   MD

170869014929 2468 Lombardi Drive San Francisco  CA

111870069510 13579 PCH            San Diego   CA

165019812149 22226 Reunion Blvd Dallas       TX

172270371229 11597 Gulf Drive    Tampa       Fl

;

run;

proc print data=customer ;

where x='Range';

run;

Thanks,

Shiva

Jay_TxOAG
Quartz | Level 8

@jen123

My comment about "data step with datalines" or as @shiva used "cards", was to provide sample data. Although, there are certainly times when uploading a file is the only way to go, providing sample data in your question makes it easier for those of us with less time to devote to answering questions.

My mistake for not being more clear...

k_laiz
Calcite | Level 5

Hi jen123,

I would like to share 3 approaches that I've tried to answer your question. Smiley Happy

Datasets:

1. Customer_Info

Customer_IDNoStreet_NameCityState
170927308313123Elks GroveChicagoIL
171540339727456Crab LakeBaltimoreMD
1700604234167890Winery LaneFrederickMD
1708690149292468Lombardi DriveSan FranciscoCA
11187006951013579PCHSan DiegoCA
16501981214922226Reunion BlvdDallasTX
17227037122911597Gulf DriveTampaFl

2. Customer_Range (I modified the values to easily see what gets extracted)

Customer_ID_LCustomer_ID_H
170000000000171000000000
110000000000120000000000

1st approach

1. Create a 'Summary' dataset merging both datasets, much like an outer join.

proc sql;

create table summary as

select *

from Customer_Info, Customer_Range

;

quit;

2. Use DATA step to do the extract of intended data and output to another dataset.

data Promoted;

set summary;

if Customer_ID ge Customer_ID_L and Customer_ID le Customer_ID_H then output;

drop Customer_ID_L Customer_ID_H;

run;

2nd approach (Use Call Execute)

1.  The main logic is to iterate through the Customer_Range dataset, i.e. per observation, comparing the Customer_ID from Customer_Info dataset, to the range.  The set of record that results to true condition is extracted to TEMP dataset.  Afterwhich, PROMOTED dataset consolidates all data for all ranges.


data _null_;

  set Customer_Range;

  call execute('

  data temp;

  set Customer_Info;

  if Customer_ID ge '||Customer_ID_L||' and Customer_ID le '||Customer_ID_H||' then output;

  run;

  proc append base=Promoted

  data=temp

  force;

  run;

  ');

run;

You can study Call Execute through http://www2.sas.com/proceedings/sugi22/CODERS/PAPER86.PDF and other sources on the web. Smiley Happy

3rd approach (Use macro facility)

1.  Create macro variables for count of observations in the Customer_Range and for the actual range values.

proc sql noprint;

select catt(count(1)) into :CustRange_Cnt from Customer_Range;

select Customer_ID_L format 13., Customer_ID_H format 13.

into :Customer_ID_L1-:Customer_ID_L&CustRange_Cnt,

  :Customer_ID_H1-:Customer_ID_H&CustRange_Cnt

from Customer_Range;

quit;

2.  Main logic is much similar to 2nd approach.  The main difference is that in this process, the macro variables will then be used as values for count and fulfilling conditions.

%macro getPromoted;

  %do i=1 %to &CustRange_Cnt;

  data temp;

  set Customer_Info;

  if Customer_ID ge &&Customer_ID_L&i and Customer_ID le &&Customer_ID_H&i then output;

  run;

  proc append base=promoted

  data=temp

  force;

  %end;

%mend getPromoted;

%getPromoted;

You may study macro in http://www2.sas.com/proceedings/sugi29/243-29.pdf and other sources in the web.

*NOTE: for 2nd and 3rd approaches, you may need to add a check if PROMOTED dataset exists, then drop/delete it before your next run since I used PROC APPEND which will just append data from previously created one.

Hope this helps answer your question.

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 10 replies
  • 3318 views
  • 0 likes
  • 6 in conversation