Hello, I have copied multiple years of data tables into an Excel xlsx file with sheets named for each year (ex. 2005, 2006, ... 2018).  I used a libname statement specifying the xlsx engine to create a SAS library that accesses the sheets from the Excel workbook.  I have run into two problems with this.  First, although I am using the firstobs= and obs= options to specify rows for data input, the data step is importing data from one row below each specified value.  For example, I wrote the program below to import rows 3-54 from the Excel sheet.  However, it brought in rows 4-55.  Does anyone know why this is happening?   My second question involves naming variables.  I tried using an input statement, as I typically use when importing raw data within data steps, but I received an error message "No datalines or infile statement".  Could anyone tell me how to specify variable names and informats when using an Excel file as a library?  I could not find an example in SAS documentation where they specified variable names.   libname SHEETS xlsx "S:\Data Files\OPA FPAR\States.xlsx"; data fpar_2005; set SHEETS.'2005'n(firstobs = 3 obs = 54); run; Also, I do not see in SAS documentation how to use the equivalent of an input state   Thanks,   Ted     ... View more

Hello, I am trying to assign values of variables from previous observations to new variables in current observations within unique ID groupings using the LAG function.  To prevent assigning the last value of previous unique IDs to the first observation for subsequent IDs, I used a SET statement with BY group processing to enable use of FIRST.ID logic to leave values for the first observation of each unique ID missing.  However, I am getting unexpected results.   For the first unique ID, I am getting missing values returned by the LAG function for the first two observations rather than just the first.  For subsequent ID numbers, I am getting the lagged value of the last observation of the previous ID assigned to the second observation of the current ID.  Below is a sample code I created.   data test; input ID Var1 $ Var2; datalines; 1 A 0 1 A 1 1 A 1 1 A 0 1 A 0 2 B 0 2 B 0 2 B 0 3 C . 3 C 1 3 C 1 3 C 0 ; run; data test_mod; set test; by ID; *Want values for first obs missing; *Want values of 2nd obs to have value of 1st obs within each ID group; if not first.ID then do; Prev_Var1 = lag(Var1); Prev_Var2 = lag(Var2); end; run;     The dataset Test_Mod looks like this.   ID     Var1     Var2     Prev_Var1     Prev_Var2 1         A            0                                    . 1         A            1                                    . 1         A            1               A                   1 1         A            0               A                   1 1         A            0               A                   0 2         B            0                                    . 2         B            0               A                   0 2         B            0               B                   0 3         C            .                                     . 3         C            1               B                   0 3         C            1               C                   1 3         C            0               C                   1         ... View more

Hello,   I would like to extract qualitative responses after encountering a comma, following Yes/No responses and create two variables, one containing yes/no responses, and the other containing qualitative responses.  Below is an example of the data as it is now, and what I would like.   Data as it is now:   ID     Var1 1      Yes. The training was great! 2      No, it was not helpful. 3      Yes 4      YES, the part about patient-centered counseling 5       No, see my previous comment 6       Yes, Amber was wonderful!!!   What I would like:   ID    Var1      Var2 1      Yes       The training was great! 2      No         it was not helpful. 3      Yes 4      Yes        the part about patient-centered counseling 5      No         see my previous comment 6      Yes        Amber was wonderful!!!   Most observations have Yes/No only, or are blank.  Only about 10% have qualitative responses included.  This was a data entry error on the part of student assistants, but it occurs fairly often across different datasets and variables. I have used the SCAN function to identify observations that need modification, but would appreciate help creating a new variable to take the qualitative value (maintaining case and punctuation as it appears).   data want; set have; if scan(upcase(VAR1,1)) in ('YES','NO') then VAR2 = 'Modify'; /*I used VAR2 as a test to see if my use of the SCAN function worked to identify observations needing modification*/ run;   I would like help assigning the qualitative values to VAR2 as they appear after the comma in VAR1, stripping the leading space, and leaving VAR1 with only the vale of Yes/No.   Thank you! ... View more

Hello,   I used PROC MEANS to sum flags by three variables (ORSID RECORDID LOG).  I now want to create new variables that retain the sums for each flag, then set each flag back to 0 or 1.  I thought it would make my program more efficient if I wrote a macro program, then used an array to run it for each flag variable as below.  The program works...… VERY SLOWLY.  Is there a better way to do this in terms of programming efficiency?   proc means data=cohort_claims_1218 sum noprint idmin; var claimflag--fp_emergency sti:; by orsid recordid log; id date; output out=flagfreqs sum=; run; %macro freqs(var); if &var ge 1 then &var._freq = &var; %mend; data test; set flagfreqs; by orsid recordid log; array FREQS claimflag--STI_Total; do i = 1 to dim(FREQS); %freqs(i); if FREQS{i} ge 1 then FREQS{i} = 1; end; run; ... View more

Hello,   I am using PROC SQL to perform a left join using four common variables to join one new variable.  The left dataset has 45,370,249 rows.  The right dataset has 14,496,317 rows.  The new table contains 45,370,500 rows.  I would like to identify which records from the right dataset are causing row duplication on the left dataset.  Could anyone offer suggestions?  Below is a copy of my program.   Thanks   proc sql; create table medical_1217 as select a.*, b.provspec from medical_1217_icd10 a left join provspec_merge b on a.orsid = b.orsid and a.recordid = b.recordid and a.log = b.log and a.file_type = b.file_type; quit; ... View more

Hello,   I am working with two very large datasets and would like to use PROC SQL to merge a variable (AdmDiag10) from a dataset (new.AdmDiag10_merge) onto another dataset to avoid unnecessary sort procedures.  The new.AdmDiag10_merge dataset has only five variables, the four need to merge the records and the new variable (AdmDiag10).  Since I am more comfortable using data steps, I am including below the program I would use (if I wanted to use a data step) to help communicate what I am trying to achieve with a left join.   /*Data step I would use to achieve desired merge*/ data medical_1217; merge medical_1217 (in=a) new.admdiag10_merge; by orsid recordid log file_type; if a = 1; run; /*SQL Procedure I have developed to attempt a similar join*/ proc sql; create table test as select a.*, b.* from medical_1217 a left join new.admdiag10_merge b on a.orsid = b.orsid and a.recordid = b.recordid and a.log = b.log and a.file_type = b.file_type; quit;   When I run the PROC SQL program, I get warning messages for each variable used to join on.  Can someone please explain what is going on here?   Thanks,   Ted ... View more