If both variables are strings, why do you need the putn function? Try to drop it: WHERE CLOSINGCYCLE = %substr(&_CYCLE.,1,6); ... View more

What do you mean by "create a macro program" ? Is Cal itself a macro  program, starting with %macro <name>(...); and ending with %mend ? Or does it generate a macro program? In first case you can just %include <path>/Cal.sas at the top of any program you want to use Cal macro program. The mcaro will be compiled at submission and used when called. In second case you need to keep the generated macro progam as a <filename>.sas in a folder then use it as described above. ... View more

1) what is the first file name you have to read? and in what folder ist it? 2) Do the folders represent all possible dates since '01jan2018' in sequence? 3) can you show a formula to calculate the excel filename using the folder name as input argument? 4) Having formula, as asked above, enables generate a SAS program to import each file separately, then to keep them as separate sas data sets or concatenate them to one big data set.   ... View more

You asked "how much effort should I put in learning SAS commands? Is it better to use PROC SQL and what I already know or using SAS language is better?"   SAS SQL is a little different then other systems' SQL. You can use SAS functions and some SAS file options within SAS SQL.  SAS Base, in some cases, is shorter in code and more efficient. Sas Base contains statements (commands), functions and many procedures.  Some of these code may differ depending on the SAS platform you use. You should look for a learning program in order to progress efficiently to get a good experience.  ... View more

Are the tables to combine, all in one folder? are the other tables in same folder? Can you select all the tables using a form (or prefix or suffix) ?   You can select table names and all their variable names by sql dictionary.columsn, ordered by column name. Export the list to excel. Check the names, decide what is the prefered name for each column and write as a new excel column, then import the updated excel table and create the rename program(s) for each table. ... View more

SAS ignores case while dealing with variable names, so "LAST_NAME" and "last_name" should be considered the same name.   SAS cannot decide what name should be for "Member Last Name" or a like. In tose tables, which seems to be imported from excel, you need to create its own step to skip titles and assign variable names, addapted to their order. ... View more

Calrifications are needed: 1) You asked for * If the previous wave answered "never," the missing value would be "never."  *. If the previous is non-missing and not "never" should it be copied to the current ? 2) How to act if 1st and 2nd (or more in sequence) are missing - should they get the first non-missing value or remain as they are ? 3) If ther are more than one missing value in sequence, should the value that was copied to the first one should be copied to the all following missing values ? (obs 13 gets value of obs 12. should it be copied to obs 14 and 15 too ?) ... View more

Are you able to download a file from sas studio to any device ? I'm using SAS ODA which is similar to sas studio. After creating a great number of text files (.sas for backup, .html for my portal) I zip them all into one archieve file and download it to a local device. If it is appropriate for you - adapt the next code to zip all your excel files from work (or any other folder) and finaly download the zipped file to the prefered device. /*********************************** /* zip all text files to an archieve /* to be downloaded as one file ************************************/ %let fref = txtfile; %let folder = <folder name with the files to download>; filename &fref "~/&folder"; %let sufx = sas; /* download program files for backup */ %let zip_name = A_text_ziped.zip; filename zipped "~/reports/&zip_name"; %let zip_ref = zipped; /** creta a list table of files to zip to archieve **/ data dir_info; length fname $40; did = dopen("&fref"); members = dnum(did); if did then do i=1 to members; fname = dread(did, i); if scan(fname,2,'.') = "&sufx" then output; end; did = close(did); keep fname; run; /** Generate a program - list of commands to execute **/ filename pgm "~/programs/ex_zipall.sas"; data _null_; length cmd $200; set dir_info end=eof; *** (1) delete old zip file ***; if _N_=1 then do; if (fexist("&zip_ref")) then rc = fdelete("&zip_ref"); file pgm; cmd = "filename &zip_ref ""~/&folder/&zip_name"";"; put cmd; cmd = cats('ods package(', "&zip_ref", ') open nopf;'); put cmd; end; cmd = cats('ods package(', "&zip_ref", ") add file=""~/&folder/" ,fname, """;"); file pgm; put cmd; if eof then do; cmd = cat("ods package(",strip("&zip_ref"),') publish archive properties(archive_name="', "A_text_ziped.zip",'" archive_path="~/reports/");'); file pgm; put cmd; cmd = "ods package(&zip_ref) close;"; put cmd; end; run; %include pgm; filename &zip_ref clear; filename pgm clear; If you don't need the excel files after downloading, you can delete them all by adapting next code: %let folder = <folder with files to remove*/; filename htm "&folder"; data _null_; retain fref "reports"; length fpath $60; did = dopen('htm'); mems = dnum(did); putlog mems=; if did and mems then do; do i=1 to mems; fname = dread(did,i); if scan(fname,-1,'.') = 'html' then do; /* putlog i= fname=; */ fpath = cats("&root/",fname); putlog fpath=; fc = filename(fref,fpath); if fc=0 and fexist(fref) then rc = fdelete(fref); msg = sysmsg(); /* putlog msg=; */ end; end; did = close(did); end; run; ... View more

Relating to your question to @Patrick "will it work if I change the target path from C:\ to network drive? " - using pipe, as much as I know, you cna't use the form of X:\ for the network drive, but you have to supply the hostname and maybe user & password in the filename statement. I believe that @Patrick  may give you an example. (Sorry, I dont remember the exact syntax). ... View more

For efficiancy you got my answer in previous post.   What for you need a reply ? Your code for export probably works fine. Is it how to copy the exported excel files to the output device? As those are no sas files you better use the OS tools for copy. PROC COPY can't be used here. In such case maybe don't use the sas work folder but define a special your own folder to export into it and copy (or move) from it to output.   To help you more (either by me or someone else) please describe your sas environment and its relation to the output device. ... View more

In case final output directory and work directory are on the same device (hard disk)  better export directly to the output directory and save the copy step, unless you have other reasons to have them in the work directory too.   In case the output directory is on a separate devices and the whole amount of data is very big you may save some overhead time by doing it in two steps, but I can't say it for sure. It depends on the hardware installation. ... View more

It should be done in two steps;  1) you can use proc freq to get the frequency of any value in a table 2) merge the proc freq result table with your original one   question - can values repeat like: 1 1 1 2 2 3 3 3 1 1 4 4. would you count 3 2 3 2 2 or 5 2 3 2  ? ... View more

Can START and END be in different years? What column shall hold the value?   Chek documentation of PROC TRANSPOSE. ... View more

Next code is tested and will do the work, supposed that t2 is not too big. Data t2; input month_; cards; 2201 2202 2203 2204 2205 2206 ; Run; proc sql noprint; select month_ into : months separated by ',' from t2; run; data _null_; mm = "&months"; nm = countw(mm); call symput('nm',compress(put(nm,2.))); run; %put nm=&nm months = &months; Data t1; input ID month wealth; cards; 1 2201 10 1 2202 20 1 2203 30 1 2204 40 2 2201 50 2 2202 60 2 2203 70 2 2204 80 2 2205 90 2 2206 100 3 2201 110 3 2202 120 3 2204 130 3 2205 140 3 2206 150 ; run; data want; set t1; by ID; retain m1-m&nm mmx1-mmx&nm; array mmx {&nm} (&months) ; array mm {&nm} m1-m&nm; drop i m1-m&nm mmx1-mmx&nm; if first.ID then do i=1 to &nm; mm(i)=.; end; if month in mmx then do; i=1; do while (i le &nm); if mmx(i) = month then do; put _N_= ID= i= month= ; mm(i)=1; leave; end; i+1; end; output; end; if last.ID then do; do i=1 to &nm; if mm(i) =. then do; wealth=.; month = mmx(i); output; end; end; end; run; You may want to add a sort of the result file by ID month;   ... View more