Hello,   I am trying to find a way to produce a total of non-overlapping (or non-stacked as synonymous terminology for this purpose) time scheduled in minutes grouped at a room_nbr and day level. Here is some simplified example course schedule data of courses who are taught within the same room on the same day, but vary in the time that they are taught (i.e. courses that are partially combined for group-work or certain lecture topics):   Room_Nbr Day Class Start_Time End_Time Minutes Overlap Non_overlap 1 Monday 101 8:00 9:00 60 0 60 1 Monday 102 8:00 10:00 120 60 60 1 Monday 103 8:00 11:00 180 120 60 2 Tuesday 201 11:00 13:00 120 0 120 2 Tuesday 202 12:00 14:00 120 60 60 3 Wednesday 301 8:00 12:00 240 0 240 3 Wednesday 302 9:00 10:00 60 60 0 4 Thursday 401 8:00 9:00 60 0 60 4 Thursday 402 11:00 12:00 60 0 60   In the example data there are 4 different rooms scheduled for 4 different days (the days themselves could be the same day since the desired result is grouped at the room_nbr level first). I have the data for room_nbr through minutes and I want to be able to produce the overlap and non_overlap fields. Looking at the example data I'd like to be able to produce a running check against previous classes for the same room_nbr and day - i.e. class 101 as the earliest course at the room_nbr and day grouping does not consider another course for determining overlapping and non-overlapping time; class 102 checks against class 101 to find time overlapped, but does not consider class 103; class 103 checks against any previous classes with the same room_nbr and day value (101 and 102). If there are 2+ courses scheduled for the same minute(s) in the same room on the same day, then I want to only count that overlapped time once in non_overlap preferably for the earliest course at the room_nbr and day level although I am open to any suggestions from you all.   Please let me know if you have any questions. I appreciate any guidance you can give me.   SAS 9.4   ... View more

Hello,   I was hoping for any ideas on the combination of two or more data sets when exporting to txt that have different fields and lengths. A variety of requests that we receive include uploading txt files where the overall row lengths change from line to line and where the delimiter shifts positions based on field length. Traditionally we have just output the different txt files and manually combined them (copy+paste the second txt file into the body of the first under the last line of the first txt file).   Example of the fake data txt file output lines with the data steps for export:   data export_1; set part_a_txt; file "\\filepath.txt" dlm=','; put id $13. survey $9. section $10. code $16. line $8. race $7. sex $6. count $11.; run;   data export_2; set part_b_txt; file "\\filepath.txt" dlm=','; put id $13. survey $9. section $10. level $8. line $8. sex $6. count $11.; run;   Resulting txt file 1: ID=######,SURVEY=1,SECTION=1,CODE=13.0000,LINE=11,RACE=2,SEX=1,COUNT=2 Resulting txt file 2: ID=######,SURVEY=1,SECTION=2,LEVEL=1,LINE=01,SEX=1,COUNT=2     Does anyone know of a way that I could essentially combine the two data steps here that I am using for a single data export step despite the fields and the overall line length being different? I understand I that I could probably just concatenate all of this together into one field, union, and export that way, but I wouldn't call that the most ideal way of handling it and I'm curious if others have any ideas.   I appreciate any advice you have to offer. Thanks.   Evan   ... View more

SAS 9.4 TS1M5   I am trying to figure out a way of exporting an excel worksheet within the same workbook as my data output that is text only. This worksheet would be a glossary of terms and general information regarding the data. In the past, I have just used extensive footnotes, but I have reached the footnote limit as the amount of data definitions needed to provide to the customer is rather extensive in this case.   I am using ods excel to export. I believe that I could create this using datalines and I am sure that most likely needs to be part of it unless I stored the text in a separate txt file and imported it which can be done, but ultimately I don't want this to appear as a table per se in the excel - no presence of a label or field name.   I assume that this is a pretty easy request, but I haven't been able to find an example that really fits what I am looking for. Does anyone have experience with creating something like this?   Thank you.     ... View more

SAS 9.4 TS1M5   Hello,   I am trying to figure out how to run multiple distinct strings of data and proc sql steps based on previous criteria found within a couple of previous proc sql steps. Below are a couple of example steps that I'd like to use as the criteria.   proc sql; create table ft_count as select count(distinct id) as count from missing_ft; quit;   proc sql; create table pt_count as select count(distinct id) as count from missing_pt; quit;   What I'd like to be able to do is view the counts as criteria that defines how the script proceeds. In this example, there would be three possibilities for the criteria:   1. A value greater than 0 in ft_count and a value equal to 0 in pt_count 2. A value greater than 0 in pt_count and a value equal to 0 in ft_count 3. A value greater than 0 in both ft_count and pt_count   What I am trying to do is to write a script that proceeds with three distinct paths that include a variety of data or proc sql steps based on each of the three possibilities above. I have a feeling that this might require some if, then, or do statements and I have read up on them, but given that this is my first foray into that territory I have thus far just been lost. If need be I can provide the further proc sql steps although I'm not certain they are really necessary for answering the question, so I will hold off on that for now.   Thank you.   Evan ... View more

Good afternoon, All:   I have two different CSV files, both identical from what I can tell in terms of data structure save out of excel, but one is reporting correctly and the other is not. Long story short there was an original CSV file, I reran the process that produces the file once some new values were added (new values that aren't a part of this particular issue from what I can tell), and now the new data set only brings up the first value on certain fields once it imports into SAS. I assume there is an issue with my CSV, but I have yet to be able to identify what it might be. I just wanted to see if anyone had a solution to the problem. Below is one example of the issue.     Original import:     proc import datafile="\\this_is_the_location_of_my_data\original_file.csv" out=basefile_original dbms=csv replace; run;   Original log:   /********************************************************************** * PRODUCT: SAS * VERSION: 9.4 * CREATOR: External File Interface * DATE: 09FEB18 * DESC: Generated SAS Datastep Code * TEMPLATE SOURCE: (None Specified.) ***********************************************************************/ data WORK.BASEFILE_ORIGINAL ; %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ infile '\\this_is_where_my_data_is\original.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ; informat EMPLID best32. ; informat APPL_NBR best32. ; informat LAST_NAME $17. ; informat FIRST_NAME $8. ; informat MIDDLE_INIT $1. ; informat SEX $1. ; informat ETHNIC_CD $1. ; informat ACAD_CAREER $4. ; informat ACAD_PROG $5. ; informat ADMIT_TERM best32. ; informat ADMIT_TYPE $3. ; informat ADMITTED $1. ; informat PROG_ACTION $4. ; informat PROG_REASON $4. ; informat PROG_STATUS $2. ; informat CENSUS_DT mmddyy10. ; informat MAJOR $9. ; informat ACADEM_LEV best32. ; informat FICE best32. ; informat HS_CODE best32. ; informat HS_GPA best32. ; informat CLASS_RANK best32. ; informat CLASS_SIZE best32. ; informat ACT_COMP best32. ; informat ACT_READ best32. ; informat ACT_MATH best32. ; informat ACT_WRIT best32. ; informat SAT_VERB best32. ; informat SAT_ESSAY $1. ; informat SAT_MATH best32. ; informat SAT_WRITE best32. ; informat SAT_EBRWS best32. ; informat SAT_MATHS best32. ; informat SAT_TOT02 best32. ; informat GRE_VERB $1. ; informat GRE_QUAN $1. ; informat GRE_ANLY best32. ; informat GRE_VERBR best32. ; informat GRE_QUANR best32. ; informat GMA_QUAN $1. ; informat GMA_TOTAL $1. ; informat GMA_ANLY $1. ; informat UTT_AMIND best32. ; informat UTT_ASIAN best32. ; informat UTT_BLACK best32. ; informat UTT_HAWAIIAN best32. ; informat UTT_HISPA best32. ; informat UTT_NONRESAL best32. ; informat UTT_NSPEC best32. ; informat UTT_WHITE best32. ; format EMPLID best12. ; format APPL_NBR best12. ; format LAST_NAME $17. ; format FIRST_NAME $8. ; format MIDDLE_INIT $1. ; format SEX $1. ; format ETHNIC_CD $1. ; format ACAD_CAREER $4. ; format ACAD_PROG $5. ; format ADMIT_TERM best12. ; format ADMIT_TYPE $3. ; format ADMITTED $1. ; format PROG_ACTION $4. ; format PROG_REASON $4. ; format PROG_STATUS $2. ; format CENSUS_DT mmddyy10. ; format MAJOR $9. ; format ACADEM_LEV best12. ; format FICE best12. ; format HS_CODE best12. ; format HS_GPA best12. ; format CLASS_RANK best12. ; format CLASS_SIZE best12. ; format ACT_COMP best12. ; format ACT_READ best12. ; format ACT_MATH best12. ; format ACT_WRIT best12. ; format SAT_VERB best12. ; format SAT_ESSAY $1. ; format SAT_MATH best12. ; format SAT_WRITE best12. ; format SAT_EBRWS best12. ; format SAT_MATHS best12. ; format SAT_TOT02 best12. ; format GRE_VERB $1. ; format GRE_QUAN $1. ; format GRE_ANLY best12. ; format GRE_VERBR best12. ; format GRE_QUANR best12. ; format GMA_QUAN $1. ; format GMA_TOTAL $1. ; format GMA_ANLY $1. ; format UTT_AMIND best12. ; format UTT_ASIAN best12. ; format UTT_BLACK best12. ; format UTT_HAWAIIAN best12. ; format UTT_HISPA best12. ; format UTT_NONRESAL best12. ; format UTT_NSPEC best12. ; format UTT_WHITE best12. ; input EMPLID APPL_NBR LAST_NAME $ FIRST_NAME $ MIDDLE_INIT $ SEX $ ETHNIC_CD $ ACAD_CAREER $ ACAD_PROG $ ADMIT_TERM ADMIT_TYPE $ ADMITTED $ PROG_ACTION $ PROG_REASON $ PROG_STATUS $ CENSUS_DT MAJOR $ ACADEM_LEV FICE HS_CODE HS_GPA CLASS_RANK CLASS_SIZE ACT_COMP ACT_READ ACT_MATH ACT_WRIT SAT_VERB SAT_ESSAY $ SAT_MATH SAT_WRITE SAT_EBRWS SAT_MATHS SAT_TOT02 GRE_VERB $ GRE_QUAN $ GRE_ANLY GRE_VERBR GRE_QUANR GMA_QUAN $ GMA_TOTAL $ GMA_ANLY $ UTT_AMIND UTT_ASIAN UTT_BLACK UTT_HAWAIIAN UTT_HISPA UTT_NONRESAL UTT_NSPEC UTT_WHITE ; if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ run;   Updated import:     proc import datafile="\\this_is_the_location_of_my_data\updated_file.csv" out=basefile dbms=csv replace; run;   Updated log:   /********************************************************************** * PRODUCT: SAS * VERSION: 9.4 * CREATOR: External File Interface * DATE: 09FEB18 * DESC: Generated SAS Datastep Code * TEMPLATE SOURCE: (None Specified.) ***********************************************************************/ data WORK.BASEFILE ; %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ infile '\\this_is_where_my_data_is\updated_file.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ; informat EMPLID best32. ; informat APPL_NBR best32. ; informat LAST_NAME $16. ; informat FIRST_NAME $9. ; informat MIDDLE_INIT $1. ; informat SEX $1. ; informat ETHNIC_CD $1. ; informat ACAD_CAREER $4. ; informat ACAD_PROG $5. ; informat ADMIT_TERM best32. ; informat ADMIT_TYPE $3. ; informat ADMITTED $1. ; informat PROG_ACTION $4. ; informat PROG_REASON $4. ; informat PROG_STATUS $2. ; informat CENSUS_DT mmddyy10. ; informat MAJOR $10. ; informat ACADEM_LEV best32. ; informat FICE best32. ; informat HS_CODE $1. ; informat HS_GPA $1. ; informat CLASS_RANK $1. ; informat CLASS_SIZE $1. ; informat ACT_COMP best32. ; informat ACT_READ best32. ; informat ACT_MATH best32. ; informat ACT_WRIT $1. ; informat SAT_VERB best32. ; informat SAT_ESSAY best32. ; informat SAT_MATH best32. ; informat SAT_WRITE best32. ; informat SAT_EBRWS $1. ; informat SAT_MATHS $1. ; informat SAT_TOT02 $1. ; informat GRE_VERB $1. ; informat GRE_QUAN $1. ; informat GRE_ANLY best32. ; informat GRE_VERBR best32. ; informat GRE_QUANR best32. ; informat GMA_QUAN $1. ; informat GMA_TOTAL $1. ; informat GMA_ANLY $1. ; informat UTT_AMIND best32. ; informat UTT_ASIAN best32. ; informat UTT_BLACK best32. ; informat UTT_HAWAIIAN best32. ; informat UTT_HISPA best32. ; informat UTT_NONRESAL best32. ; informat UTT_NSPEC best32. ; informat UTT_WHITE best32. ; format EMPLID best12. ; format APPL_NBR best12. ; format LAST_NAME $16. ; format FIRST_NAME $9. ; format MIDDLE_INIT $1. ; format SEX $1. ; format ETHNIC_CD $1. ; format ACAD_CAREER $4. ; format ACAD_PROG $5. ; format ADMIT_TERM best12. ; format ADMIT_TYPE $3. ; format ADMITTED $1. ; format PROG_ACTION $4. ; format PROG_REASON $4. ; format PROG_STATUS $2. ; format CENSUS_DT mmddyy10. ; format MAJOR $10. ; format ACADEM_LEV best12. ; format FICE best12. ; format HS_CODE $1. ; format HS_GPA $1. ; format CLASS_RANK $1. ; format CLASS_SIZE $1. ; format ACT_COMP best12. ; format ACT_READ best12. ; format ACT_MATH best12. ; format ACT_WRIT $1. ; format SAT_VERB best12. ; format SAT_ESSAY best12. ; format SAT_MATH best12. ; format SAT_WRITE best12. ; format SAT_EBRWS $1. ; format SAT_MATHS $1. ; format SAT_TOT02 $1. ; format GRE_VERB $1. ; format GRE_QUAN $1. ; format GRE_ANLY best12. ; format GRE_VERBR best12. ; format GRE_QUANR best12. ; format GMA_QUAN $1. ; format GMA_TOTAL $1. ; format GMA_ANLY $1. ; format UTT_AMIND best12. ; format UTT_ASIAN best12. ; format UTT_BLACK best12. ; format UTT_HAWAIIAN best12. ; format UTT_HISPA best12. ; format UTT_NONRESAL best12. ; format UTT_NSPEC best12. ; format UTT_WHITE best12. ; input EMPLID APPL_NBR LAST_NAME $ FIRST_NAME $ MIDDLE_INIT $ SEX $ ETHNIC_CD $ ACAD_CAREER $ ACAD_PROG $ ADMIT_TERM ADMIT_TYPE $ ADMITTED $ PROG_ACTION $ PROG_REASON $ PROG_STATUS $ CENSUS_DT MAJOR $ ACADEM_LEV FICE HS_CODE $ HS_GPA $ CLASS_RANK $ CLASS_SIZE $ ACT_COMP ACT_READ ACT_MATH ACT_WRIT $ SAT_VERB SAT_ESSAY SAT_MATH SAT_WRITE SAT_EBRWS $ SAT_MATHS $ SAT_TOT02 $ GRE_VERB $ GRE_QUAN $ GRE_ANLY GRE_VERBR GRE_QUANR GMA_QUAN $ GMA_TOTAL $ GMA_ANLY $ UTT_AMIND UTT_ASIAN UTT_BLACK UTT_HAWAIIAN UTT_HISPA UTT_NONRESAL UTT_NSPEC UTT_WHITE ; if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */ run;     When looking at one result from the original import I get this:   SAT_MATH SAT_WRITE SAT_EBRWS SAT_MATHS SAT_TOT02 550 490 650 590 1240   All numbers are as desired and match the CSV that I imported. All three fields are informat BEST32., format BEST12.     When looking at the same result in the updated imported data set, I get this:   SAT_MATH SAT_WRITE SAT_EBRWS SAT_MATHS SAT_TOT02 550 490 6 5 1     As you can see it is only producing the first value of the field for the three columns to the right. The two fields to the left are coming in the same informat and being put into the same format as the original file, but the three fields to the right have an informat of $1. This is where the problem lies, but the updated CSV itself is no different from the original from what I can tell (all fields are in 'General' format when viewed in Excel).     Thank you in advance for your help.     SAS 9.4 TS1M1   ... View more