@Ok6 wrote: Thanks for your response. I have same problem and after using the code, I got an error: "physical file does not exist". What could be responsible? This is usually caused by not using the actual name of the file. You might simply have a typo.  Remember that on Unix file names are case sensitive.  So myfile.xpt is not the same file as MyFile.XPT.   You might be looking for the file in the wrong directory (or "folder").   You might be using the name the file has on your PC and not the name that the file has on the machine were SAS is running.  Remember if you are using a tool like SAS/Studio or Enterprise Guide to compose and submit your SAS programs that it is the machine where SAS itself is running and not the machine that is running your user interface that is important.   Remember when use a LIBNAME statement with the XPORT engine that SAS does not know whether your intention is to READ from the libref that is created or WRITE to it.  So the LIBNAME statement will not fail if the file does not exist.  Instead the failure will come when you try to read from the file that the libref pointed to. ... View more

@data_null__ wrote: I notice that SAS has given the MYFMT format the NOTSORTED attribute in the CNTLOUT data set, HLO='SM' even though you did not specify NOTSORTED in the VALUE statement options list.   I did not expect that output. ... I think that it what the paper you cited means when it talks about sorting. Essentially when you using the LABEL statement of PROC FORMAT without the NOTSORTED option for a multilabel format it sorts by the START value and then generates the control dataset in that order and set the 'S' tag of the HLO variable to preserve it.   You can see it by replicating the AGE format from that paper. proc format cntlout=formats; value age (multilabel) 19-120='Adults' 1-18='Children' 1-4='Preschool' ; value ages (multilabel notsorted) 19-120='Adults' 1-18='Children' 1-4='Preschool' ; run; data compare; N+1; set formats(where=(fmtname='AGE') rename=(start=AGE_START end=AGE_END label=AGE_LABEL)); set formats(where=(fmtname='AGES') rename=(start=AGES_START end=AGES_END label=AGES_LABEL)); run; proc print; var N age: ; run;   ... View more

Figure out how SAS stores the value for a MLF in a CNTLIN= dataset by making one by code and looking at the dataset generated by the CNTLOUT= option. proc format cntlout=formats; value myfmt (multilabel) 1 = "One" 2 = "Two" 3 = "Three" 4 = "Four" 5 = "Five" 1,3,5 = "Odd" 2,4 = "Even" 1-5 = "1-5" ; run; proc print data=formats; var fmtname type start end label hlo sexcl eexcl ; run; So basically just add the extra observations you need and set the HLO variable 'SM'. ... View more

You probably need to follow this: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/grstatproc/n18szqcwir8q2nn10od9hhdh2ksj.htm   ... View more

Hard to tell what your issue is without working example. Can you provide a small dataset to demonstrate the issue? Or use one of the available datasets in the SASHELP library. ... View more

Yes. That should solve the issue of SAS changing the values so that Excel will split the values into multiple lines.  Which you can see in this screen shot of the two different files. Which is not what I took your original question to be about.   ... View more

If you want to flag ID's then why is the value of FLAG different on some of the observations for ID 0001 and 0003?   If not then why isn't FLAG=1 on the second observation? I has OTHER_TRT='Y' and it is not '1st line'.   In general if you want to generate a new variable for all of a BY group you have two choices. Have all of the information needed for calculation available on the FIRST observation in the group.  Then RETAIN the NEW variable so its value stays the same on all observations in the BY group. Use two do loops to process the observations for each BY group twice. In the first loop gather the information needed to make the calculation.  And in the second loop re-read and output the observations so all of the observations in the BY group get the newly calculated value. data want; do until (last.id); set have; by id; * gather information ; end; * calculations at the BY group level ; do until (last.id); set have; by id; * calculations at the observation level ; output; end; run;   ... View more

@_Hopper wrote: Will this approach still work if the supplier gets their act together and has the values formatted the correct way the next time around? What I want is a permanent solution not one that fixes this issue and then the issue appears again when (if) the input file is corrected.    PS: I'm not allowed to modify the source data (e.g., save it as csv). I have to use it as is. No.  In that case you will need a little more logic to detect if the DateTime variable is numeric or character.   For example you might use this %VARINFO()  macro to detect the type of the variable and then use it to determine whether or not you need to take action. data want; set have; %if %varinfo(HAVE,DATETIME,TYPE)=C %then %do; ... logic to generate ACTUAL_DATETIME ... rename actual_datetime=DateTime ; drop DateTime; %end; run; You might also want to look into using a different file format for the transfer. Perhaps SAS transport files?  That way there is no conversion to be done. Or a delimited text file.   That way you can write a data step to read the file and set the variable names, types and lengths they way you want them.   ... View more

Yes   ... View more

This issue is that you generated this code: college="A&S" But there was no S macro variable found.   To avoid the macro processor examining the values embed the strings in single quotes, not double quotes.  You can include the quotes into the value of the macro variables you generate by using the QUOTE() function in your SQL query. %macro cagoal; %local i; proc sql noprint; select distinct quote(trim(college),"'") , quote(cats("&path\",Global_College,"_Goals.xlsx"),"'") into :college1- , :Global_College1- from caa.usf_college_sort ; quit; %do i=1 %to &sqlobs; proc export dbms=xlsx replace data=caa.usf_college_sort ( where=(college=&&college&i) keep=college global_college cohort_yr usf_pell_6yr_actual Pell_6YR_Actual Pell_6YR_Forecast l95 u95 ) outfile=&&Global_College&i ; run; %end; %mend cagoal;   ... View more

@Ksharp wrote: Tom, You only take care of date part of it, not time part, right ? No.   Excel stores time of day as a fraction of day.  The DHMS() function converts a fraction of day into the corresponding number of seconds.   Essentially it is doing: 60*(60*(24*D + H) + M) + S ... View more

When you have both character and numeric values in a column in EXCEL then SAS is forced to define the variable as character.  When this happens with DATE or DATETIME values SAS store the actual numeric value EXCEL uses as a normal digit string.   Best solution is to fix the Excel file so that all of the values in that column are actual Excel datetime values.   But if you are stuck with the file you have just convert the character variable into a new numeric variable.  Then adjust for the difference in the way that EXCEL and SAS count days. data want; set have; actual_datetime = input(DateTIme,??32.); if missing(actual_datetime) then actual_datetime=input(DateTime,anydtdte20.); else actual_datetime = dhms(actual_datetime+'30DEC1899'd,0,0,0); format actual_datetime datetime19.; run; So when the value looks like a number then just add the number of days between 1960 and 1900 to adjust for the difference in what day each system thinks is day one (and the fact that Excel thinks 1900 was a leap year) and convert the number of days into number of seconds.   Otherwise just ask SAS to convert the string into a datetime value. ... View more

You have left the SAS session that SAS/Studio uses to actually RUN your code in a state where it is waiting for you to complete a statement or comment.   The fastest way to fix it is to restart the SAS session.   You can do it from the menu or you should be able to use the F9 function key.  There is no need to logout of SAS/Studio.   Note that will lose all of your WORK datasets. ... View more

I like to put long sections into the Spoiler Tag.  Click on the triangle with an ! in it to create the spoiler section.  Then place the code block inside it. Example: * Insert code block here ; ... View more