Hi Monika, The query builder doesn't scan the entire table to determine distinct values in a column when you select the show values button.  By default EG 5.1 is set up to scan only up to 100,000 rows in an input source for performance reasons.  You can change this setting by using Tools->Options->Query.  In that dialog you can find the 'Maximum number of rows to process for distinct value requests' and it can be changed.  This causes the SAS Server to have run through a lot more records when performing a distinct value request through query builder.  With a lot of users doing this, it can become a drag on the system. You can also use the Tasks->Describe->One Way frequencies and analyze distinct values of a column that way.  This will not be limited by the option set in the same area. Hope this helps, Russ ... View more

Here is one idea rather than the proc sql subquery update method you have above: proc sql; create table file_out as select a.Name,           case when b.tloc ^= . then b.tloc else a.zipcode end as zipcode,           case when c.tedpt2 ^= . and b.tarea1 ^= 'AYA' then c.tarea2 else a.area1 end as area1,           a.dept,           a.ldper,           case when c.tdept2 ^= . and b.tarea1 ^= 'AYA' then c.areadesc else a.ldesc end as ldesc,           case when b.tloc = . then 1 else 0 end as miss_ind_21,           case when c.tdept2 = . then 1 else 0 end as miss_ind_32 from file1 a left join file2 b on a.name = b.tname                    left join file3 c on a.dept = c.tdept2; data final_file(drop=op miss_ind_21 miss_ind_32);      set file_out;      length op $70;      if miss_ind_21 = 1 then do;            op = 'NAME NOT FOUND: '||name;            put op;            delete;      end;      if miss_ind_32 = 1 then do;           op = 'DEPT NOT FOUND IN FILE3 '||dept;           put op;           delete;      end; run; ... View more

Here are a couple of ideas: You can index the commonly used columns of the huge tables.  If you are using a filter or join on an indexed column, SAS will not process the entire set sequentially.  You can add indices with PROC DATASETS. For the where clause, use the most exclusive filters first if there are multiple conditions.  That is, specify the filters first for those that remove the most rows from the source set. Avoid joining large tables together until you have filtered down the inputs (if possible) Avoid cross joins and aggregates on the data source until you have filtered it down to only what you need (if possible) I agree with ballardw on the use of the count.  If you are performing filters against your large set to get a count, you are probably forcing the code to run through the input set twice.  Though if you think there may be no results at times, this may be something that helps performance overall. ... View more

You could potentially perform a transpose depending on your data, or you could use a data step with multiple output statements: data output(keep=householdID, age);      set input;      age = age_1;  output;      age = age_2;  output;      age = age_3;  output;      ... run; The output statements forces an observation to be written to the output set each time it is encountered.  Just one idea ... View more

I believe the stored process session is different than the current EG session, even when executing from within EG. Try directing it to a library other than WORK. ... View more

How about something like this: /* order your input set */ proc sql; create table part_output as select * from input order by id, categ_1, categ_2, date desc; /* select records using by processing and a retained flag (twrot) */ data myoutput;      set part_output;      retain twrot;      by id;      if first.id and categ_1 = 'a' then do;            twrot = 0;            output;      end;      else if twrot = 1 then do;            twrot = 0;            output;      end;      if last.id then do;            twrot = 1;      end; run; ... View more

Hi Alexsander S, I work with the SAS Stored Processes Web server a lot but I never tried this prompt type.  I found the exact same issue you did using 9.3.  I would also be interested if anyone has a solution for this. Regards ... View more

The first thing I always do when planning out a Windows Task Scheduler process through SAS is verify that the VBS script kicks off the job and I get a log when it is executed directly. So, skip the scheduling step and verify that when executed, the VBS script is actually running SAS.  Just double click the generated EGScript.vbs file that was created by SAS and check for the log where you directed it. If this isn't working, try the following: uncomment the line in the vbs script that says:  " 'MsgBox strmsg 'Uncomment this line if you want to be notified via MessagBox of Errors in the script " Just remove the single-quote in front of the 'MsgBox strmsg line. Try and execute the vbs script again by double clicking on it.  You will receive a message popup if there are any issues.  Try that and we can surely help further. I've been at sites where security prevented VBS scripts from running, and even where security prevented the scripts from writing to temporary folders.  This all becomes very difficult to identify when testing through the scheduler itself. Regards ... View more

Hi, I believe you may have the 'Generalize this import step to run outside of Enterprise Guide' option set on the 4 page of the import data task wizard.  By default, this sets a maximum record length of 256.  This should be affecting the output of the import task though, causing a truncation of records. Regards ... View more

I'll assume that you are scheduling through the Windows Task scheduler with your SAS EG project.  I would suggest first verifying that the export works without issue when you run the project from Enterprise Guide.  I'll assume you've already done that.  In that case, make sure that you are using the 'Export as a Step in the project' option to create your export task in EG.  I imagine you have already done this as well. So, If this is already the case, and you are having success executing the VBS scheduled script but are receiving an error on that particular portion of the project, look closely to see if that error may be caused by the scheduled processes inability to access the drive location you specified in the export.  For example, If you run the scheduled task while you are not logged in to the machine, it is likely that file server drives are not accessible to the process.  In many cases, these drives are only available when you are logged in and recognized by Windows Directory services.  If the scheduled process is running as you, with a stored password, it is also possible that the drives themselves are not mapped to drive letters and are therefore inaccessible.  Often, organizations map drive letters to file servers using a batch script when you log in to windows.  This same thing doesn't happen when the Windows Task Scheduler logs in as you to execute a task. ... View more

Try looking at it this way: PROC SQL; CREATE TABLE TEMP_PROC1 AS SELECT PX.REFERRAL_ID,                CASE WHEN PX.LINE = 1 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD,                CASE WHEN PX.LINE = 1 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME,                CASE WHEN PX.LINE = 2 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_2,                CASE WHEN PX.LINE = 2 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_2,                CASE WHEN PX.LINE = 3 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_3,                CASE WHEN PX.LINE = 3 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_3,                CASE WHEN PX.LINE = 4 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_4,                CASE WHEN PX.LINE = 4 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_4,                CASE WHEN PX.LINE = 5 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_5,                CASE WHEN PX.LINE = 5 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_5,                CASE WHEN PX.LINE = 6 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_6,                CASE WHEN PX.LINE = 6 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_6,                CASE WHEN PX.LINE = 7 THEN EAP.PROC_CODE ELSE '' END AS PROC_CD_7,                CASE WHEN PX.LINE = 7 THEN EAP.PROC_NAME ELSE '' END AS PROC_NAME_7 FROM DATABASENAME.dbo.REFERRAL R LEFT JOIN REFERRAL_PX AS PX ON R.REFERRAL_ID = PX.REFERRAL_ID LEFT JOIN DATA_EAP AS EAP ON PX.PX_ID = EAP.PROC_ID WHERE R.START_DATE >= '06/01/2015' AND START_DATE <= '06/30/2015'; Then followed by a group By PROC SQL; CREATE TABLE TEMP_PROC2 AS SELECT REFERRAL_ID,                MAX(PROC_CD) AS PROC_CD,                MAX(PROC_NAME) as PROC_NAME,                MAX(PROC_CD_2) AS PROC_CD_2,                 MAX(PROC_NAME_2) as PROC_NAME_2,                MAX(PROC_CD_3) AS PROC_CD_3,                MAX(PROC_NAME_3) as PROC_NAME_3,                MAX(PROC_CD_4) AS PROC_CD_4,                MAX(PROC_NAME_4) as PROC_NAME_4,                MAX(PROC_CD_5) AS PROC_CD_5,                MAX(PROC_NAME_5) as PROC_NAME_5,                MAX(PROC_CD_6) AS PROC_CD_6,                MAX(PROC_NAME_6) as PROC_NAME_6,                MAX(PROC_CD_7) AS PROC_CD_7,                MAX(PROC_NAME_7) as PROC_NAME_7, FROM TEMP_PROC1; The original author is using the aggregate (MAX) with a group by to collapse seven rows of data into one while retaining the proc_cd and name values. TEMP_PROC1 looks like: REFERRAL_ID     PROC_CD     PROC_NAME     PROC_CD_2     PROC_NAME     PROC_CD_3     PROC_NAME .... 012345                V935              Surgery 012345                                                                  N0032               Anesthesia 012345                                                                                                                       V4333               Appendectomy Which when aggregated (MAX) by REFERRAL_ID in TEMP_PROC2, becomes this: REFERRAL_ID     PROC_CD     PROC_NAME     PROC_CD_2     PROC_NAME     PROC_CD_3     PROC_NAME .... 012345                V935              Surgery               N0032               Anesthesia          V4333               Appendectomy                                                                                                                     ... View more