I am using a table value function in sas code to pull back a database table as of a certain date.  The database uses a timestamp format of YYYY-MM-DD HH:MM:SS and SAS is not wanting to use that or any equivalent when actually running the query.  A sample is below. proc sql; create table TableAsOf_Jan162020 as select * from Libname.TableNameAsOf('2020-01-16 08:00:00'); quit; I keep getting this error or something similar depending on the attempts at changing the format in the ( ). ERROR: Invalid option name '2020-01-16 08:00:00'.    All help appreciated! ... View more

I have a data set which is the result of a matrix/likert/grid table in a survey. However, I received the data in a strange format and am having trouble re-parsing it into separate columns of data - 1 column per likert prompt.     In the sample data, row one has all the right columns of data and the answers are just following it in separate brackets (possible answers are VERY IMPORTANT4, 3, 2, Not AT ALLIMPORTANT1), but as you can see, the following rows have some missing data.     Any thoughts on how to get a column of result data per prompt and show missing data where appropriate?  I am pretty rusty on string matches and don't really know how to get the missing information to show as missing when not there.  Create separate tables and merge things back together?   Thanks!   Sample Data is here: DATA WORK.Book1; LENGTH ID 8 Results $ 145 ; FORMAT ID BEST12. Results $CHAR145. ; INFORMAT ID BEST12. Results $CHAR145. ; INFILE DATALINES4 DLM='7F'x MISSOVER DSD ; INPUT ID : BEST32. Results : $CHAR145. ; DATALINES4; 1{Spousal considerations}{VERY IMPORTANT4}|{Lifestyle}{VERY IMPORTANT4}|{Hours}{VERY IMPORTANT4}|{Financial considerations }{NOT AT ALLIMPORTANT1} 2{Spousal considerations}{VERY IMPORTANT4}|{Hours}{VERY IMPORTANT4}|{Financial considerations }{NOT AT ALLIMPORTANT1} 3{Spousal considerations}{VERY IMPORTANT4}|{Lifestyle}{3}|{Financial considerations }{NOT AT ALLIMPORTANT1} ;;;; ... View more

  data WORK.TestData; infile datalines truncover; input ID:$8. Type:$1. Color:$32. Quarter:32. Score:32.; datalines; A11 1 G 1 99 A11 1 G 2 98 A11 1 G 3 100 A11 2 B 1 97 A11 2 B 2 90 A11 2 B 3 90 A12 1 G 1 99 A12 1 G 2 88 A12 1 G 3 48 A12 1 G 4 59 A13 1 G 1 99 A13 1 G 2 88 A13 1 G 3 48 ;;;;;;;;;; So, in this data or data like it, i need a Quarter 4 represented for each person (unique ID) and each "type".  Even if the score would be empty I need to insert a row with a Quarter 4 and the above data to match it (again score as missing is fine).  This was problematic with creating an extra data set and merging back in because i had 2 "types" for one person in the data set. A PROC SQL would only merge in the top most Type of the Q4 data.  A retain statement was able to copy back in the data, but again, only for the first Type if more than one was present.     Thoughts are most appreciated. ... View more