I have the following data and am trying to use the scan function to find a 10 character string starting with C-   PLP C-00201123 042718 OR State Change (1) X C-00200838 042718 supplies surprise day 5 offer (1) PLP C-00201124 042718 NJ State Change (1) X C-00200834 042618 supplies surprise day 4 offer (1) PTR C-00200836 042618 Encompass Subscribers Credit Card Expiring Soon (1) PTR C-00200835 Inter-Intra Email 4-26-18 (1) PTR C-00200837 042618 ELD Roadside Inspections Guide (1) PTR C-00200953 Encompass Customer Webcast  4-25-18 Reminder (1) TOD C-00120194 042518 TOD Newsletter - April 2018 (1) PHR C-00200833 FMLA Invite 4-25-18 (1) PCS C-00200831 042518 Fall Protection for Construction (1) PTR C-00200832 042518 Roadcheck 2018 Whitepaper (1) X C-00200830 042518 supplies surprise day 3 offer (1) X C-00200827 042418 supplies surprise day 2 offer (1) PWS C-00200829 KOL Invite (Cintas)  4-24-18 (1) PWS C-00200828 KOL Invite 4-24-18 (1) C-00200943 - Free 2290 Poster 04-16-2018 (3) PTR C-00200524 042318 Encompass Appreciation Offer (1) X C-00200826 042318 supplies surprise day 1 offer (1) PTR C-00200954 ELDs and IFTA 4-23-18 (1) C-00201191 Online Renewal - 1st Notice - sub expiring soon (2)   The following code gives me the data in two steps.  I was just wondering if there was another way to write this in one step?  If not, I can work with this. Thanks!   NEW_CODE=scan(Sent_Mailing_Name,'1','C-'-2); NEW_CODE2=scan(Sent_Mailing_Name,'2','C-'-2);   NEW_CODE  NEW_CODE2 PLP                C-00201123 X                     C-00200838 PLP                C-00201124 X                    C-00200834 PTR               C-00200836 PTR               C-00200835 PTR               C-00200837 PTR               C-00200953 TOD              C-00120194 PHR              C-00200833 PCS              C-00200831 PTR                C-00200832 X                      C-00200830 X                      C-00200827 PWS                C-00200829 PWS                C-00200828 C-00200943    - PTR                 C-00200524 X                     C-00200826 PTR                C-00200954 C-00201191    Online PTR                C-00201056 X                     C-00201050 C-00201191    Online       ... View more

I have not written many stored processes in SAS EG.  I did create one to store code that is used multiple times in many sets of code with the idea that if changes are needed, I could change it in one place for all code.  I was hoping that the output could just be sent to a work. file.  The code runs fine when I use the work. file but it appears to not be saving the data as I cannot reference the  work.  file further in the code.  If I use a  permanent SAS dataset it runs fine.  I was hoping to just create a temporary work file though.  Is it possible to just create a work file using Stored Processes?   ... View more

I have the following data:   C-00118247                                         Prom_Code     Mat_1     Mat_2     Mat_3      Mat_4    Mat_5      Mat_ 6    Mat_7   ......Mat_70 C-00118619    47375     47379     47377     47376     28763     27033     27034             C-00118512    60205                                    C-00118528    50001     50206     50006     C-00118839                                         C-00118865                                         C-00118868   30796                                       C-00118856                                         C-00119741   38010      27033                                    C-00119649                                         C-00119448                                         C-00119449                                         C-00120397    347     744     I am looking to get this output:   Promo_Code  Material C-00118247     C-00118619    47375 C-00118512    60205 C-00118528    50001 C-00118839    C-00118865     C-00118868    30796 C-00118856     C-00119741    27033 C-00119649     C-00119448   C-00119449     C-00120397    347   C-00118247     C-00118619    47379 C-00118512     C-00118528    50206 C-00118839     C-00118865     C-00118868     C-00118856     C-00119741    27033 C-00119649     C-00119448     C-00119449     C-00120397    744     C-00118247     C-00118619    47377 C-00118512     C-00118528    50006 C-00118839     C-00118865     C-00118868     C-00118856     C-00119741    C-00119649     C-00119448     C-00119449     C-00120397      etc.....        I am currently using the following code, creating separate small files and appending them together. When there was only 5 or so, it was not so bad, but there are now 70....any ideas on a simpler way to do this? Thank you!   PROC SQL;    CREATE TABLE WORK.MATERIAL_1 AS    SELECT t1.PROMO_CODE,           t1.MATERIAL_1 as t1.MATERIAL       FROM WORK.QUERY_FOR_FIX_PLANNER_FILE t1; QUIT;        PROC SQL;    CREATE TABLE WORK.MATERIAL_2 AS    SELECT t1.PROMO_CODE,           t1.MATERIAL_2 as t1.MATERIAL       FROM WORK.QUERY_FOR_FIX_PLANNER_FILE t1; QUIT;      PROC SQL;    CREATE TABLE WORK.MATERIAL_3 AS    SELECT t1.PROMO_CODE,           t1.MATERIAL_3 as t1.MATERIAL       FROM WORK.QUERY_FOR_FIX_PLANNER_FILE t1; QUIT;          PROC SQL; CREATE TABLE WORK.APPEND_MATERIALS AS SELECT * FROM WORK.MATERIAL_1  OUTER UNION CORR SELECT * FROM WORK.MATERIAL_2  OUTER UNION CORR SELECT * FROM WORK.MATERIAL_C3; QUIT;                                                                                                                     ... View more

I have two data sets and want to do a one to many merge to get the  Material field below.  The problem is that the field I want to join with is not a complete field - it is only part of the join in the other data set.  Is there a way to join using a like or contains within the join? I read that an inner join would work but it does not.  Thank you!   PLANNER_FILE Prom_Cd         Level3_A C-00118868    ACCIDENT C-00120397    HANDBOOK   MATERIAL_DATA LEVEL3                               MATERIAL CANADIAN HANDBOOKS    892 CANADIAN HANDBOOKS    1015 CANADIAN HANDBOOKS    13546 CANADIAN HANDBOOKS    13555 CANADIAN HANDBOOKS    41968 FMCSR HANDBOOK    765 FMCSR HANDBOOK    7545 FMCSR HANDBOOK    43212 FMCSR HANDBOOK    4003162 FMCSR HANDBOOK    4011027 HANDBOOK KITS    37649 HANDBOOK KITS    37651 HANDBOOK KITS    37652 HANDBOOK KITS    37653 HANDBOOK KITS    37655 HANDBOOK KITS    37657 HANDBOOK KITS    37658     Current Code - no output   PROC SQL;    CREATE TABLE WORK.QUERY_FOR_FIX_PLANNER_FILE_0002 AS    SELECT t1.PROMO_CODE,           t1.LEVEL3_A,           t2.LEVEL3,           t2.MATERIAL AS MATERIAL1       FROM WORK.PLANNER_FILE t1            INNER JOIN WORK.MATERIAL_DATA t2 ON (t1.LEVEL3_A = t2.LEVEL3); QUIT;   ... View more