About Mahtalii

Mahtalii · ‎04-19-2018

Hello All, I want to do a simple sum() function over some variables in my tables. And since I have so many tables, I want to write one macro code for all of them. The problem is, not all the tables have all the variables! And if you run sum(a1, a2, a3) while for example, "a1" doesn't exist in the table, you'll get an error! I wonder if there is any way to tell SAS to sum(a1, a2, a3) for those that exist in the table. For example, if "a1" doesn't exist, then sum(a1, a2, a3) should give me sum(a2, a3). And if none of them exist then missing value. Any suggestion is much appreciated. Cheers,

Mahtalii · ‎10-30-2017

Hi @PGStats, Sorry for my late reply. I made my data smaller by deleting very old records that were not needed, and the code worked perfectly. But since you asked, here is the log for the error I received when I was trying to run the code on the whole big table (with dates from 1900 to 2060). Thanks again, ERROR: Insufficient space in file WORK.A.DATA. NOTE: The DATA step has been abnormally terminated. NOTE: There were 7571824 observations read from the data set WORK.Have. WARNING: The data set WORK.A may be incomplete. When this step was stopped there were 4865641041 observations and 3 variables. NOTE: DATA statement used (Total process time): real time 28:54.77 user cpu time 6:26.15 system cpu time 55.31 seconds memory 1823.15k OS Memory 32140.00k Timestamp 2017-10-27 09:50:11 AM Step Count 50 Switch Count 6

Mahtalii · ‎10-30-2017

Thanks @Ksharp. Sorry for my late reply. The problem is, after running your code both observations 5 and 6 will appear in the 'key' table, and will be excluded from the 'want' table which is not desirable. One thing I can do is to get rid of those semi-duplicates (they differ in addresstype) first and then run your code. Thank you,

Mahtalii · ‎10-26-2017

Thanks @Ksharp! This is a very short and efficient way to get the result on data that is actually like my example. However, my real data has some records in which everything is the same except for address type. Something like this: 5 2 M Feb2000 Jan2003 A1A1A1 6 2 R Feb2000 Jan2003 A1A1A1 And this will get rid of both records! Thank you very much.

Mahtalii · ‎10-26-2017

Thanks @PGStats ! This is great! However, with the big table that I have, I keep running out of space and I need to find a way to deal with that. Do you have any suggestions? Thanks again for your help.

Mahtalii · ‎10-23-2017

Hi, I have a database of individuals' addresses in SAS like this: OBS ID addresstype effectivedate enddate postalcode 1 1 M Feb2000 Jan2003 A1A1A1 2 1 R Jan2003 Jan2020 B2B2B2 3 1 M Feb2016 Sep2016 C3C3C3 4 1 M Sep2016 Jan2020 D4D4D4 Addresses are either residential (R) or mailing (M). For each individual and for each period of time, if I have the residential address I want to keep that and delete any mailing address in that period of time. If for a period of time there is no residential address recorded, then I will keep the mailing address. For the individual of this example I want the clean data to look like this: OBS ID addresstype effectivedate enddate postalcode 1 1 M Feb2000 Jan2003 A1A1A1 2 1 R Jan2003 Jan2020 B2B2B2 Is there any way in SAS to create intervals of time based on two dates (effectivedate and enddate in this example) and then compare them against each other. Thanks in advance for your help.

Online Status	Offline
Date Last Visited	‎03-17-2021 01:39 PM

How to sum IF the variables exist

Re: Creating time intervals based on two date variables

Re: Creating time intervals based on two date variables

Re: Creating time intervals based on two date variables

Re: Creating time intervals based on two date variables

Creating time intervals based on two date variables

Re: Adding descending sequence number

Re: Creating time intervals based on two date variables

Re: Creating time intervals based on two date variables

How to sum IF the variables exist

Re: Creating time intervals based on two date variables

Re: Creating time intervals based on two date variables

Re: Creating time intervals based on two date variables

Re: Creating time intervals based on two date variables

Creating time intervals based on two date variables