I think what your SQL code does is something like this:   Data data_final; length line 8 rpt_order 8 MBR_MAID $15 Member_id $15 Member_Name $255 Provider_Name $255 Network_Status $255 CHECK_NUMBER $25 CHECK_DATE 8 CLAIM_ID $12 REVENUE_CODE 8 Service_Description $255 DIAGNOSIS_CODES $255 SERVICE_FROM_DT 8 SERVICE_THROUGH_DT 8 SUM_OF_DAYS 8 SUM_OF_CLAIM_AMT 8 SUM_OF_PAID_AMT 8 PAYOR $255 Rate 8 ; format line 8.0 rpt_order 8.0 CHECK_DATE yymmdd10. SERVICE_FROM_DT yymmdd10. SERVICE_THROUGH_DT yymmdd10. ; keep line--rate; set data_order(in=base) data_totals(in=sum); by MBR_MAID; line=_N_; if base then do; rpt_order=Member_Order; Member_id=MBR_MAID; Network_Status='In Network'; Service_Description='MH-InPatient-Psychiatric Srvcs R&B'; end; else do; rpt_order=Member_Order; Member_id=0; call missing(Member_Name,Provider_Name,Network_Status,CHECK_NUMBER,CHECK_DATE,CLAIM_ID,REVENUE_CODE,Service_Description,SERVICE_FROM_DT,SERVICE_THROUGH_DT,PAYOR,Rate); DIAGNOSIS_CODES='Total'; end; run; The first lines (down to and including the KEEP statement) is analogue to the CREATE TABLE statement.     The code assumes that both DATA_ORDER and DATA_TOTALS are sorted by MBR_MAID. ... View more

One possibility is to start your parallel tasks with the SYSTASK  statement, and then have your main program wait for all tasks to finish, using WAITFOR : SYSTASK '<command to start task 1>'; SYSTASK '<command to start task 2>'; SYSTASK '<command to start task 3>'; WAITFOR _ALL_; /* code for reports goes here */ The linked documentation here is for Windows, but I think the facility is available under UNIX as well. ... View more

I think the one error you made is that you did not save the generated data as RESULTS.OUTPUT27, but as WORK.OUTPUT27. ... View more

I think you made a small mistake in your macro code. You forgot to give the X variable a value, I suppose it should be the current NUM value that you are looking for, e.g.: %macro sqlloop; %local x; PROC SQL; %DO Num=1 %TO &max ; %let x=%scan(&numvar,&num); CREATE TABLE sle_&x as SELECT t1.EMPLID, t1.ACCOMPLISHMENT as ACCOMPLISHMENT_&x, t1.RESULTS_LEVEL_CD as RESULTS_LEVEL_CD_&x FROM WORK.SLE_RESULT_HIST_rank t1 where t1.Num = &x; %END; QUIT; %mend sqlloop; Another possibility is to use a parameter, as you first thought of, and then use SQL to create the relevant macro calls: %macro sqlget(x); PROC SQL; CREATE TABLE sle_&x as SELECT t1.EMPLID, t1.ACCOMPLISHMENT as ACCOMPLISHMENT_&x, t1.RESULTS_LEVEL_CD as RESULTS_LEVEL_CD_&x FROM WORK.SLE_RESULT_HIST_rank t1 where t1.Num = &x; QUIT; %mend sqlget; I renamed the macro from SQLLOOP to SQLGET, as there is no loop now. To execute all the SQL calls, just use proc sql; select distinct cats('%SQLGET(',t1.Num,')') into :sqlcalls separated by ';' from WORK.SLE_RESULT_HIST_rank t1; QUIT; &sqlcalls; ... View more

Sounds like you hit upon a function name which is used in some other product - I cannot seem to find anything about that function name, though, so it may be some prototype stuff that has not yet come into production, and quite possibly never will. ... View more

Assuming your input data is sorted by person_id, a fast way to do it is this: data admin_nvx admin_adult2; merge have(where=(mx='NVX') in=NVX) have; by person_id; if nvx then output admin_nvx; else output admin_adult2; run; ... View more

You can get the filename of the infile currently being read using the FILENAME option, e.g. data raw; infile 'c:\dir\*.csv' filename=fnam <and other options, like delimiter>; csvfile=fnam; /* the FILENAME variable is not saved to output */ input <your input statement>; run; You can then check against the directory you got with the pipe (or use DOPEN and DREAD, that's more portable), and see how many records came from each file.   Another possibility is to use the FILEVAR and EOF options to read the files - assuming the directory scan data is WORK.FILES, you can do something like this: data raw(drop=noofrecords) linesread(keep=noofrecords <filename variable>); set files; infile dummy filevar=<filename variable> eof=done <and delimiters etc>; do noofrecords=0 by 1; input <your input statement>; output raw; end; done: /* EOF makes the program jump here when there are not more records */ output linesread; run; The reason why the EOF option is used here is that there will also be an output for empty files (noofrecords=0) this way.   ... View more

Apart from what @Kurt_Bremser wrote, your code as shown will not work anyway. When you have your macro variable right, the line INFILE 'Path\ScannedFiles_&lastDayofTheLastMonth1..txt' delimiter=',' dsd; should be changed to  INFILE "Path\ScannedFiles_&lastDayofTheLastMonth1..txt" delimiter=',' dsd; as macro variables inside single quote are not resolved. ... View more

Of course, if you have more than 2 visits on the same time, you will still get too many combinations. In that case, it may be simpler with a datastep, e.g. proc sort data=visits; by patient_id visit_time; run; data duplicates; set visits; by patient_id visit_time; if not(first.visit_time and last.visit_time); run; ... View more

If it is a self join, I assume you are joining on something like a.patient_id=b.patient_id and a.visit_time = b.visit_time and a.visit_text ne b.visit_text If you do not want the output both ways, just put change the "not equal" to "greater than" or "smaller than", e.g. a.patient_id=b.patient_id and a.visit_time = b.visit_time and a.visit_text < b.visit_text   ... View more

It looks like your data is read from a number of infiles, row by row.   In that case, it may be (just guessing) that your data is grouped (but not sorted, because of the filename) already. Then it may be a possibility to use NOTSORTED: proc transpose data=tall out=wide(drop=_name_); by filename row notsorted; id name; var value; run; But your output variables will still be character, as the input variable is character.   If you need to convert the data to numeric, you can code the stuff in a datastep, as suggested by @Kurt_Bremser , although I would probably code it like this: data wide; do until(last.row); set tall; by filename row notsorted; select(upcase(name)); ... when('ICYTOTOXIC') Icytotoxic=input(value,1.); ... otherwise /* maybe write a warning in the log here */; end; end; drop name; run; ... View more

Something like this, i suppose: proc sort data=have; by year product_id; run; data want; merge have(where=(year=2022)) have(where=(year=2023) keep=year product_id sales rename=(sales=sales23)); by product_id; if sales23>0 then YoY=(sales23-sales)/sales23; drop sales: year; format YoY percent7.0; run; This will get the numbers you showed, but like @Oligolas I think the correct calculation would be  YoY=(sales23-sales)/sales ... View more

That is very hard to answer, as your picture is so small that the text is unreadable.    Unless you created the file in WORK, that normally gets deleted at the end of the session. ... View more