Home

CeciliaE

Hello, I have a couple of case when statements that will be used in about 18 different proc SQL programs within a project. Rather than copying and pasting the case when statements in each program, I'm wondering if there's a way for me to create a macro that stores the case when statements so that I can reference the macro from each of the programs. The first macro is quite simple. I have a variable that has a few invalid values that must be mapped to valid ones. However, the vast majority of observations can remain as they are. My case when statement is structured as follows: (CASE /*if variable is an invalid value, return its valid equivalent*/ WHEN VARIABLE = INVALID_VALUE1 THEN VALID_VALUE1 WHEN VARIABLE = INVALID_VALUE2 THEN VALID_VALUE2 ... /*otherwise, return the variable as-is*/ ELSE VARIABLE END) as VARIABLE_CORRECTED, This is what I've tried, but it didn't work: %macro variable_crosswalk(variable); %if &variable = 'invalid_value1' %then %let ; &variable = 'valid_value1'; %end; %else %if &variable = 'invalid_value2' %then %do; &variable = 'valid_value2'; %end; ... %else %do; &variable %end; %mend; The second case when statement I'd like to store in a macro is a little more complicated. It uses several different race/ethnicity fields to calculate a person's race. It's structured as follows: (case when race1 = 1 then 'Race 1' when race8 = 2 then 'Race 2' ... when race1 = 1 and race2 = 2 then 'Two or More Races' when race1 = 1 and race3 = 3 then 'Two or More Races' /*all other possible race combinations*/ ... else 'Unknown' end) as Race_Ethnicity, Is there a way for me to store one or both of the case when statements above as macros that can be accessed from all 18 programs in my project? If so, can someone help me with the syntax? Thanks for any assistance you can provide!

genemroz

Esteemed Advisors: I am trying to interleave two datasets with a condition that the resulting dataset contains only observations that can be found in both of the two datasets. Below is exemplar code to illustrate the problem. If you run this code and inspect dataset interleave2 you will see that for a group of 3 observations where target=1, two came from Random_A and one came from Random_B. Likewise, for a group of three observations where target=2, two came from Random_B and one came from Random_A. All of these observations need to be retained in the desired dataset. For the group of 3 observations where target=3, all observations came from Random_B only. These are ones that need to be omitted. All observations for a given target that come from a single source dataset are not to be retained in the desired dataset. The challenge for me (and now for you) is to come up with the code that will interleave Random_A and Random_B such that the resultant dataset that only contains the groups of targets that are present in both datasets. Hope this makes sense and thanks for taking a look, Gene data Random_A (drop=i); call streaminit(4786); do i=1 to 100; Source="A"; Target=rand("Integer",1,100); ST=catx('/',Source,Target); output; end; data Random_B (drop=i); call streaminit(6874); do i=1 to 150; Source="B"; Target=rand("Integer",1,100); ST=catx('/',Source,Target); output; end; Proc sort data=Random_A; by ST; run; Proc sort data=Random_B; by ST; run; data interleave1; set random_A random_B; by ST; run; proc sort data=interleave1 out=interleave2 nounikey; by target; run;

santisouknee

Dear All, I am quite a new user using SAS software. Please give me some suggestion base on the scenario below: 1. I have time series data for almost 2 years, which records the date and the amount of money that earn daily but in this case user record the data only weekdays (data for sat and sun is empty). 2. Base on the data i have, i try to identified, include dummy variables, estimate and got the satisfied model to do forecasting. This also include the cosscorr pamarameter. 3. However, when i try to run forecasting by using proc arima data=data ; identify var=money crosscorr=(Monday Tuesday Thursday Friday ); estimate input=(Monday Tuesday Thursday Friday ) p= (0) (21) q= (1) (0); forecast lead=30 interval=day id=date out=results; run; i got an error "The value for option LEAD= has been reduced to 0." and the results that i got is generate only for the trained variable. The lead forecasting is not generate for me. I am not sure the forecast syntax is working with my data struture or not. Please feel free to leave some comments on this or please share some ideas how can i solve this issue. Regards, santisouk

MargaretC · ‎05-28-2023

Learn the best practices and importance of upgrading to SAS 9.4 M8.

sassy_seb

Hello, I am getting the following error messages when trying to merge two datasets. One of the datasets I am getting from a csv file, so maybe the issue could be there? I was trying to specify the length of the PID variable for the redcap_sort dataset from the redcap one, which is the one we got from the csv file. However, I keep getting messages that the variable has multiple lengths and it keeps truncating the data. Any PID after 999 gets shortened. So 1000 and 1001 become 100, 1010 becomes 101, etc. Any help or a nudge in the right direction would be greatly appreciated, thank you so much. Edit: The programming with the csv file already has: data work.redcap; %let _EFIERR_ = 0; infile &csv_file delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=1 ; informat pid $500. ; informat pid_ini $500. ; and the code for format: format pid $500. ; It has this for all the variables. I thought the above code would make it so that the variables would have that limit of 500 characters?