Folks,   I have three numeric variables which range from 0 to n and I would like to compare if they match across records and if not which way to they disagree.    The name my variables are A , B and C. I've drawn the logic out in a 3 way Venn Diagram but I'm trying to write a if clause in SAS to extract out the information. I have 7 combinations in total. A B C A=B A=C B=C A=B=C   I would like to have something written such as; if A=B=C then Cohort='ABC' if A=B then Cohort='AB' and so on.   Any help on this would be great.  ... View more

Folks,   I have been computing mean and medians using the following statement. proc summary data = Quantity_of_Properties_1 nway completetypes; ; format class $class. year_filed year_filed. county_y $county_y. child_filed $child_filed.; class class year_filed county_y child_filed/mlf; var realprice1; output out = Table_0_fil (drop=_TYPE_ /*_freq_*/) median= med_price mean=mean_price; run; However, I would like to compute the geometric mean, but I understand I can only do it through proc surveymeans. Is there anyway to get the same kind of output as above with proc surveymeans?   I've tried to the following but it does not work so any help would be great.   PROC SURVEYMEANS data=prices geomean; format class $class. year_filed year_filed. county_y $county_y. child_filed $child_filed.; class class year_filed county_y child_filed; var realprice1; ods output statistics=i; run;     ... View more

I need to sort by two variables to keep the most valid one to the bottom. I then need to output this record to a dataset. How do I go about this? See example which is failing.   All the best,   *Sort dataset by the variable payslip_ref (unique payslip id) and also time_stamp (when record came into the system or changed in system); proc sort data=█ by time_stamp payslip_ref ; *With the block sorted by payslip_reference and time_stamp we tell SAS to keep the last unique record and also output previous records; data latest_data obsolete_data; set █ by payslip_ref; if last.payslip_ref then output latest_data; else output obsolete_data; ... View more

I have some information in my dataset which I have written into a macro variable. When I want to include this information in an automated email but the issue is I want one row per obs.   proc sql noprint; select distinct i_oth into :PMOD_LIST1 separated by " " from work.data1; quit; %put NOTE: &=PMOD_LIST1; My issue is that it looks like this; NOTE: PMOD_LIST1=APR_19 Obs=6336566 APR_20 Obs=172 AUG_19 Obs=7308518 DEC_19 Obs=6571416 FEB_19 Obs=6201178 FEB_20 Obs=6045254 JAN_19 Obs=6707812 JAN_20 I would like it to look like this; NOTE: PMOD_LIST1= APR_19 Obs=6336 APR_20 Obs=12677 AUG_19 Obs=73085 DEC_19 Obs=65716 FEB_19 Obs=62018 FEB_20 Obs=5678 JAN_19 Obs=67012 JAN_20 Obs=72269 So in a sense my separator is a row. This makes it look better in an email to users. Any help would be appreciated. ... View more

Folks, I have developed a project for my processing of quiet an intensive workload. I have broken it into 4 separate programs.   At the very top of my programs I tell SAS to print the log to a specific location. proc printto log="location\\Parsing_&date..txt"; run; At the end of the program I have. proc printto; run; What I would like to do is have SAS check these logs and send me an email if it finds an error in them. I have seen this code online. I would like to augment the below to actually pull what the error is too. I would also like to add another condition to check if the code is still running. So I would have condition 1 - An error occurred 2 - Code is still running 3 - The job ran to completion Can anyone provide any advice? %macro send_mail; filename mymail email 'myemail' subject='Processing'; %if &syscc>0 %then %do; data _null_; file mymail; put 'An ERROR has occurred in the code'; run; %end; %else %do; data _null_; file mymail; put 'The job ran to completion'; run; %end; %mend; ... View more

Folks,   I have a large dataset of circa 107 million obs and size of 250Gb. As the dataset is large to process I break it into chunks such as the code below highlights.   %macro blocking(block,clean,block_key); *We read in the dataset called latest_records, which has no exact duplicates (the same record twice or 2+) and output to a sepearte dataset depending on the what the block is i.e. 0,1,2,3..; data █ set TEMPS.latest_records; if var4= &block_key then output █ run; *Sort dataset by the variable payslip_ref (unique payslip id) and also time_stamp (when record came into the system or changed in system); proc sort data=█ by payslip_ref time_stamp; *With the block sorted by payslip_reference and time_stamp we tell SAS to keep the last unique record and also output previous records; data latest_data obsolete_data; set █ by payslip_ref; if last.payslip_ref then output latest_data; else output obsolete_data; %mend blocking; %blocking(block_0,temps.clean_0,'0'); %blocking(block_1,temps.clean_1,'1'); %blocking(block_2,temps.clean_2,'2'); %blocking(block_3,temps.clean_3,'3'); %blocking(block_4,temps.clean_4,'4'); %blocking(block_5,temps.clean_5,'5'); %blocking(block_6,temps.clean_6,'6'); %blocking(block_7,temps.clean_7,'7'); %blocking(block_8,temps.clean_8,'8'); %blocking(block_9,temps.clean_9,'9'); %blocking(block_b,temps.clean_b,' '); Each block is around 15 million obs. I was wondering is there a more efficient way to process this?   What I was thinking was that each time the macro runs the dataset temps.latest_records gets reduced i.e. when it runs the second time temps.latest_records would have no observations from the dataset called clean_0.    Is something like this possible? Would appreciate any other ideas about how to deal with large scale dataset processing.     ... View more