I am having a big performance issue with the macro I 've written, Extremely slow (no actual output) when runs with large input data sets., Appreciate any help in re writing the code. My code is as below. %macro getHistAllPlanteam(PPID,s_start_date); data pt_all_planteam_temp (keep=ParentPartyID All_Planners g_snapshot_start_date g_snapshot_end_date ); set &_INPUT1 (where=(Contact_planner ne "n/a" and ParentPartyID="&PPID" and g_snapshot_start_date="&s_start_date"dt)); by ParentPartyID Plan_mgmt_team_member; length All_Planners $255; if first.ParentPartyID then do;   retain All_Planners;   All_Planners=cat(trim(Plan_mgmt_team_member)," (",trim(Participant_Management_Role_Desc),")"); end;   else do All_Planners=catx("; ",trim(All_Planners),cat(trim(Plan_mgmt_team_member)," (",trim(Participant_Management_Role_Desc),")")); end;   if last.ParentPartyID then output; run; proc append base=&_OUTPUT1 data=pt_all_planteam_temp force; run; %mend getHistAllPlanteam; proc sql; delete from &_OUTPUT1; run; Data _Null_; set &_INPUT2; st_dtmval=put(g_snapshot_start_date,DATETIME.); call execute('%getHistAllPlanteam('||ParentPartyID||','||st_dtmval||')'); run; Below is a sample input; &_INPUT2 ParentPartyID  g_snapshot_start_date      g_snapshot_end_date 1-104O5G        26FEB15:00:00:00          01JAN99:00:00:00 &_INPUT1 ParentPartyID      g_snapshot_start_date      g_snapshot_end_date                          Plan_mgmt_team_member  Participant_Management_Role_Desc  1-104O5G            26FEB15:00:00:00            1JAN99:00:00:00                                    David Addison                  Primary Planner                                                                                                  1-104O5G            6FEB15:00:00:00              01JAN99:00:00:00                                  Rachael Mayne                    Plan Support Coordinator Expected output: ParentPartyID      All_plan_mgmt_team                                                                                                  g_snapshot_start_date          g_snapshot_end_date 1-104O5G            David Addison (Primary Planner); Rachael Mayne (Plan Support Coordinator)      26FEB15:00:00:00                  01JAN99:00:00:00 ... View more

Hi All, I am trying to import a test file of about 1000k records, Fields are correctly delimited with pipe character but some contains control characters in between. This caused unsuccessful file import.  Is there any way to load this file. Sample as below.First line with field names. Pln_Tmpl_Assmnt_Itm_Id|Answer_Text|Guideline_Text|Plan_Template_Assessment_Id|PlanTemplateAssessmentItemCmt|Question_Text|Sequence_Nbr|Source_Created_Date|Source_Created_By|Source_Last_Updated_Date|Source_Last_Updated_By|Source_System_Code|Record_Ownership_Code|g_snapshot_start_date|g_snapshot_end_date|g_extract_sk|g_extract_name|g_current_record_ind|g_deleted_record_ind|g_hash_md5|g_insert_job_sk|g_update_job_sk|g_insert_datetime|g_update_datetime 2-XDQC6W||"Record Goal 1 in 'comments' field and the outcome this relates to in 'answer' The text in the ""comment"" field will appear verbatim in the participant plan. It should reflect the goal in the participant's own words."|2-XDQC6V||Goal 1||01OCT2013:11:55:24.000|2-KMAOFW|01OCT2013:11:55:24.000|2-KMAOFW|Siebel|2|02OCT2013:00:00:00|16OCT2013:23:59:59|32|20131002|N|N|2BCA91FA567BF629D3361CBD38BD0EEA|1334||10MAR2015:11:27:36| 3-6FER8D|2|record details in comments including whose answer is recorded (particpant or guardian/parent/carer etc)Answer of 1 - 5 where 1 = very dissatisfied and2 = dissatisfied3 = neither satified nor dissatisfied4 = satisfied5 = very satisfied|3-6FER8C||Overall how satisfied are you with your level of community involvement?|1|05NOV2013:14:22:19.000|2-G32QYC|17DEC2013:13:28:41.000|2-G32QYC|Siebel|2|30MAR2014:00:00:00|01JAN5999:00:00:00|205|20140330|Y|N|43799D96C07315900EE81FD96B874B15|1334||10MAR2015:11:27:36| 3-6FER8E||record details in comments including whose answer is recorded (particpant or guardian/parent/carer etc) Answer of 1 - 5 where 1 = very dissatisfied and 2 = dissatisfied 3 = neither satified nor dissatisfied 4 = satisfied 5 = very satisfied|3-6FER8C||Overall  how satisfied are you with the study you have completed?||05NOV2013:14:22:19.000|2-G32QYC|05NOV2013:14:22:19.000|2-G32QYC|Siebel|2|06NOV2013:00:00:00|10NOV2013:23:59:59|67|20131106|N|N|D94237279E68D5AE74D7D46E25FD309C|1334||10MAR2015:11:27:36| 3-6FER8E|||3-6FER8C|record details in comments including whose answer is recorded (particpant or guardian/parent/carer etc) Answer of 1 - 5 where 1 = very dissatisfied and 2 = dissatisfied 3 = neither satified nor dissatisfied 4 = satisfied 5 = very satisfied|Overall  how satisfied are you with the study you have completed?||05NOV2013:14:22:19.000|2-G32QYC|05NOV2013:14:22:19.000|2-G32QYC|Siebel|2|11NOV2013:00:00:00|17DEC2013:23:59:59|71|20131111|N|N|0454683AC52A5DABAF11435F28637E26|1334||10MAR2015:11:27:36| 3-6FER8E|5||3-6FER8C|record details in comments including whose answer is recorded (particpant or guardian/parent/carer etc) Answer of 1 - 5 where 1 = very dissatisfied and 2 = dissatisfied 3 = neither satified nor dissatisfied 4 = satisfied 5 = very satisfied|Overall  how satisfied are you with the study you have completed?||05NOV2013:14:22:19.000|2-G32QYC|17DEC2013:13:28:48.000|2-G32QYC|Siebel|2|18DEC2013:00:00:00|29MAR2014:23:59:59|103|20131218|N|N|C09BD225FE07B3DAACD275259216108E|1334||10MAR2015:11:27:36| It is actually switch line character like  \r\n  or \n . I was able to attached the file at last. ... View more

How can I implement rank function in SAS Proc sql which will give below output? GroupNo Rank 1               1    1               2    1               3 2               1 2               2 3               1    3               2 3               3     I need to use it in SAS DI without using custom coding, which will happen if I didn't use proc sql( or let me know if it can be done in another way in SAS DI by using metadata only). Yet there is no simple proc sql implementation guess will have to use the data step approach. Thanks all. ... View more