@RW9 wrote: Nice.  Though to be fair, it is better to model the data in a way which allows multiple rows per block of data rather than across the page, this is pretty standard in CDISC models for instance.  For example this data could be represented: ID SEQ NAME LAST AGE 1 1 abc temp 12 1 2 def temp2 43 1 3 efg temp3 34 In this method the information is all present, easy to work with, and is expandable infinitely (almost), whereas columns run out or become unwieldy and coding is far harder. Of course the way to go. Normalized data is to be preferred. Intelligent data makes for intelligent programs. ... View more

Since I was already using heavy artillery, here is some more:   data have; input id :$12.; datalines; 5-12344 5-1234 5-123456 7-345 2-3456 ; proc sql; select id, put(input(prxchange("s/(\d+)\-(\d+)/\200\1/o", 1, id),10.),z10.0) as newId from have; quit; ... View more

Do be aware that email is a really poor medium for distributing data.  You could hit size or other restrictions, you also have multiple copies out there, no control, email is not secure etc.  Use a web portal, with access controls. ... View more

Without data we can't test code.   Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against. ... View more

From your description, three cases are possible: 1- Tran before Enrol 2- Tran during Enrol 3- Tran after Expiry This should handle all:   data have; infile datalines missover; input ID (TranDate EnrolDate ExpiryDate) (:date9.); format TranDate EnrolDate ExpiryDate yymmdd10.; datalines; 12345 30DEC2014 01JAN2015 31DEC2015 Tran before first Enrol 12345 04JAN2015 01JAN2015 31DEC2015 12345 03DEC2016 01DEC2016 30NOV2017 12345 04FEB2017 01APR2017 31MAR2018 12345 03JAN2018 01JAN2018 31DEC2018 12345 03JAN2019 01JAN2018 31DEC2018 Tran when expired ; proc sql; select unique a.ID, a.TranDate, b.EnrolDate, b.expiryDate, intck('DAY', a.TranDate, b.Expirydate) as DaysLeftBeforeExpiry from have as a left join have as b on a.ID=b.ID and b.enrolDate <= a.tranDate group by a.ID, a.TranDate having intck('DAY', a.TranDate, b.Expirydate) = max(intck('DAY', a.TranDate, b.Expirydate)); quit; ... View more

@CamRutherford wrote: I get the following error message... ERROR: Physical file does not exist, /opt/sasconfig/grd/Lev1/SASApp/O:\EUROMKTG\MARKETING ANALYTICS DEPT\CAMPAIGN REPORTING\4. Q2 REPORTING\TEST\CLICKS.CSV. From that error message it is easy to see that your SAS session is running on Unix and not Windows. So any path that uses drive letters will not work.  You either ask your networking to have the file share that you have mapped to the O: drive on your Windows machine mounted somewhere on the Unix servers that SAS is using. Or just copy the file somewhere first and import the copy. ... View more

@CamRutherford wrote: Hi @Tom& @Reeza   The whole Idea is that I want the code to automate the process of importing files into SAS so using the EG menus is a manual process.   Is there any way I can import if my SAS sits on a unix server through method of coding?   Thanks, Cam   Once the files are available on the server where SAS is running it is easy to automate reading all files from a directory (there are dozens of questions on that subject on this forum).  So either mount the folder with the files on the Unix server.  Or setup an FTP server that can see the files and use FTP to move the files to the Unix server. You can even have SAS ftp the files for you, or read the files via the FTP engine on the FILENAME statement so that you don't need to save copies of the raw files on the SAS server. ... View more

A format is a tool for telling SAS how to display the values of the variable.  It is NOT how you define a variable. A SAS variable is either a fixed length character string or a floating point number.  In an ARRAY statement you can optionally define the type and length of the variables, but there is no way to attach a format to the variables using the ARRAY statement.  To attach a format you need use a FORMAT or ATTRIB statement.   Note that when specifing a variable's length you do not need to include a period.  Periods are used when specifying a format so that the parser can distinquish them from variable names or other keywords.  SAS will silently ignore the extra periods that you had included after the lengths in your ARRAY statements.   proc sql noprint; select max(nobs) into :max_obs trimmed from (select sbr_id,count(*) as nobs from deposits group by 1) ; quit; data deposits_array_base; array resort (&max_obs) $500; array interv (&max_obs) $500; array startdt (&max_obs) 8; array enterdt (&max_obs) 8; array region (&max_obs) $500; array channel (&max_obs) $500; format startdt: enterdt: date9. ; do flag=1 to &max_obs until (last.sbr_id); set deposits; by sbr_id; resort(flag) = resort_id; interv(flag) = interval; startdt(flag) = start_dt; enterdt(flag) = enter_dt; region(flag) = prim_region; channel(flag) = entry_opr_id; end; drop flag row spb_type spb_stat xch_spb_rel_no resort_id interval start_dt enter_dt prim_region entry_opr_id ; run;   ... View more

Well, it is possible.  What you have to remember is that SQL is different from SAS, SAS treats each obsevation step by by step, and if sorted then you can do this calculation.  SQL treats observations as a bunch of observations - order is only given by logical sort, not by logical order in a data table.  So you can use the monotonic() function like this: proc sql; create table SCORE as select SBRID, monotonic() as COUNTER from WEEKS.TEST; quit; However, if you have the data: SBRID 001 001 001   There is no logical way of telling SQL that the order is: SBRID  COUNTER 001       1 001       2 001       3   Rather than: SBRID  ORDER 001      3 001       1 001       2   Or any other combination.  Is there a reason why you need to do this in SQL, generally speaking for SQL an observation count in itself is not really useful, aggregate functions are of more use (sum, count etc.). ... View more

Have you tried doing the libname type I posted above, this creates a native .xlsx file?  As you have 9.4 that should work fine.   Also, your code: PROC EXPORT DATA= class outfile= "/user/&user/test.xlsx" dbms=xlsx replace; sheet="Class"; PROC EXPORT DATA= cars outfile="/user/&user/test.xls" dbms=xls; sheet="Car"; run; You are outputting to first xlsx and second to xls.  ... View more

Hi @CamRutherford You've asked for code which exports SAS tables into sheets in a single Excel Workbook but the code you've posted creates a tab delimited text file per source SAS table.   The code I've posted creates this single Excel you've asked for. It's a replacement for your code between below lines of comment in your code.   /*preparing the files for email distribution*/ <here the code creating the Excel> ***update email recipients here***; .... attach=("&path/mult_sheet_by.xlsx") ....       "I don't have a path location" In the code you've posted there are paths as well. Your code executes on a SAS server and there is certainly a file system there (not the same than what you get in your local environment though) where you can write data to. You've been using /user/&user so should this be an existing path then use this one. %let path = /user/&user;   ... View more

If you want this in a data set what is the purpose of the blank values for year? What will you do with the resulting data set? ... View more

It does work   data new; input month $; cards; January February March April May June July August September October November December ; run; data new1; set new; new_col='01'; new_col1=upcase(substr(month,1,3)); new_col2=put(today(),year4.); fin_col=new_col || strip(new_col1) || trim(new_col2); run; ... View more

@CamRutherford wrote: Thank you - do you know how I can reference UK maps in SAS? All the documentation I see is for US. Anything you see that references MAPS.US (or MAPS.USCOUNTY) you can likely replace with MAPS.UK but you need to check the different Id variables. Maps.UK2 has some regional descriptions for the ID in UK, a 'feature table' dataset.     ... View more