Hello,   If the input variable contains a comma, then the containscomma variable should be set to 1:    data _null; input = "Why, oh why"; if findw(input, ",") > 0 then do; containscomma=1; end; else containscomma=0; put containscomma; run; It does contains a comma, yet containscomma is set to 0.   Can someone please advise on how I can make the code work as intended? ... View more

Hello,   We have a table that's updated daily. It's updated by means of SCD2 without 'close out records'. This means that when a row is deleted from the source table, it remains as-is in the target table.   Now we'd like to add a new column named DELETED_IN_SOURCE_FLG. It should be set to '1' if the row no longer exists in the source table.   Example: A source table named SRC_EMAIL, and a target table named EDS_EMAIL, with the primary key EMAIL_ID.   What would be the most efficient way to say "if an EMAIL_ID exists in EDS_EMAIL, but no longer exists in SRC_EMAIL, then set DELETED_IN_SOURCE_FLG = 1"?   The only way I can think of at the moment sounds a bit cumbersome. Select all distinct EMAIL_ID from the source table, and from the target table, then join them and flag the ones that don't have a match, and write that to the target table. Is there a better way? ... View more

Can someone please define the difference between a BASE file and a DATA file? I surprisingly have a very hard time finding any information on this. Is the DATA file the physical file, whereas the BASE file is the metadata file?   In example, an error table fails with these warnings: WARNING: Variable CustomerId was not found on DATA file. WARNING: Variable CustomerType was not found on BASE file. The variable will not be added to the BASE file. ... View more

I have an ETL-job that runs each day. It creates a csv-file that needs to be deleted at the end of the job.   I can delete a specific file using this code:   data _null_; filename delfile 'c:\temp\file_2017_03_23.csv'; rc = fdelete('delfile'); run; However I don't want to indicate one specific file, because the filename will vary each day.    I'd like to be able to do something like this:    data _null_; filename delfile 'c:\temp\file_&datestamp..csv'; rc = fdelete('delfile'); run; /*the &datestamp would always refer to the current day*/   However, the filename statement doesn't accept macro variables.   Do you have any advice on how I can resolve this? ... View more

Hello,   I'd appreciate some SAS code help (again).   First some basic info: - A Policy can have several rows (one for each Policy Version). - A Policy can be associated with 1 or more Cases - Cases can be associated with the same, or different, Activities   What I need is a new column which indicates if it's the 1st, 2nd, 3rd, etc., time the policy becomes part of the same activity. This only happen when the policy has two or more cases within that activity (a policy only 'enters into an activity' by means a a case).   Examples: - Policy 587 and 726 only have one case (i.e. they only 'enter into an activity' once), and therefore has WANT=1. - Policy 699 has two cases: 748 and 889. Both of these cases are associated with the same activity (123). Case 748 was created first (it has the lowest policy version id), therefore it gets WANT=1. Case 889 was created second, and gets WANT=2 (it's the 2nd case associated with the same policy and activity).     POLICY_ID POLICY_VERSION_ID ACTIVITY_ID CASE_ID WANT 587 1 123 289 1 587 2 123 289 1 587 3 123 289 1 587 4 123 289 1 699 1 123 748 1 699 2 123 748 1 699 3 123 748 1 699 4 123 889 2 699 5 123 889 2 726 1 877 733 1 726 2 877 733 1   Hopefully I explained well enough. Thanks for your time. 🙂 ... View more

I have a POLICY-table which triggers a new row (policy_version) each time a change is made to the policy.   I need a new column (WANT) which always displays the most recent policy version where the policy_code was either 15 or 70.   POLICY_ID POLICY_VERSION POLICY_CODE WANT 123456 1 0   123456 2 0   123456 3 0   123456 4 70   123456 5 0 4 123456 6 0 4 123456 7 0 4 123456 8 15 4 123456 9 0 8 123456 10 0 8 123456 11 0 8 123456 12 0 8 123456 13 0 8   Example: The last row (policy_version 13) sees that the most recent policy_version with policy_code 15 or 70 was policy_version 8.   Would appreciate help on this, thanks for your time. ... View more