Hi,
For csv purpose and sending the csv to others I need to have a colomn in the csv that is schedule_dt. This may contain a datetime or can be empty. But when empty it really should be empty.
Now I have a space. This cause a import fail, because a space is a string and not a date.
Does anyone know how to make sure the schedule_dt is empty (no space) in this code?
Thanks!
proc sql;
create table expclang.&_file_name._export as
SELECT DISTINCT T2.CUSTOMER_ID,
CASE WHEN T2.RESERVEREN = 'N' THEN 'Jouw artikel is weer leverbaar'
WHEN T2.RESERVEREN = 'Y' THEN 'Jouw artikel kun je vanaf nu reserveren' END AS SUBJECTLINE,
CASE WHEN T2.RESERVEREN = 'N' THEN 'We hebben goed nieuws voor je, een artikel waar je om hebt gevraagd is er weer'
WHEN T2.RESERVEREN = 'Y' THEN 'We hebben goed nieuws voor je, een artikel waar je om hebt gevraagd kun je vanaf nu reserveren' END AS PREHEADER,
'ENTnlmcmproduct20180101017552' AS REFERRER,
'89,103' AS FLEXBANNERS,
T2.GLOBAL_ID AS GLOBALIDS,
'46,47' AS TEXTS,
'' as SCHEDULE_DT,
'[{"ID":1,"PARAM":"METADATA","CONTENT":{"SNIPPETCODE":"HEADER","USP":"1","SUBJECT":"0","UNSUBSCRIBE":"1"}},
{"ID":2,"PARAM":"METADATA","CONTENT":{"SNIPPETCODE":"TEXTBANNER","SNIPPETVAR":"FULL",'
|| CASE
WHEN T2.RESERVEREN = 'N' THEN '"IDX1":"0"'
ELSE '"IDX1":"1"'
END
|| '}},
{"ID":3,"PARAM":"METADATA","CONTENT":{"SNIPPETCODE":"TEXT","MANUAL":"1",'
|| CASE
WHEN T2.RESERVEREN = 'N' THEN '"IDX1":"0"'
ELSE '"IDX1":"1"'
END
|| '}},
{"ID":4,"PARAM":"METADATA","CONTENT":{"SNIPPETCODE":"PRODUCTGROOT","PRICE1":"1","DELIVERY1":"1","IDX1":"0"}},
{"ID":5,"PARAM":"METADATA","CONTENT":{"SNIPPETCODE":"FOOTER","SNIPPETVAR":"DEFAULT","DISCLAIMER":"Je ontvangt deze e-mail eenmalig omdat je je op '
|| TRIM (T2.DATUM_SET_ALERT)
|| ' met dit e-mailadres hebt aangemeld voor een leverbaarheidsalert voor dit artikel. Indien je bij meerdere artikelen een alertverzoek hebt ingediend en je wenst deze niet meer te ontvangen, kun je op de productpagina van het artikel de alert uitzetten."}}]'
AS CONTENT
FROM EXPCLANG.&_FILE_NAME._NO_CNTRL_GRP T1
INNER JOIN MDB.MCM_CPA_EXP T2
ON T1.CUSTOMER_ID = T2.CUSTOMER_ID ;
quit;
data _null_ ;
/*FILE "&file_export_wait_s" lrecl=4000 DLM= ';' ; */
FILE "&file_export_ready_s" lrecl=4000 DLM= ';' ;
if _n_ = 1 then put @1 "CUSTOMER_ID;SUBJECTLINE;PREHEADER;REFERRER;FLEXBANNERS;GLOBALIDS;TEXTS;SCHEDULE_DT;CONTENT";
set expclang.&_file_name._export;
put (_all_) (~);
run ;
You need the DSD option on your FILE statement.
Also, unless you WANT to add a lot of unnecessary quotes to your file, do not use the ~ formatting modifier in the PUT statement. SAs will automatically add quotes when they are NEEDED.
You do need SOMETHING in the format list. It could be the : modifier instead of the ~ modifier. Or a pointer movement command.
put (_all_) (+0);
The DSD option does prevent the space, so that's cool.
But I do get a lot of unnecessary double quotes. So now they are double, double. Tried to find the right formatting modifier, but haven't been able to find it yet.
Any suggestions?
Thanks a lot anyway for the DSD option
What extra quotes? If you use the ~ modifier then SAS will quote the values. If the values already have qutoes in them then those will be double, otherwise the resulting file cannot be parsed.
WHY do you need to add quotes in your CSV file? CSV file do NOT need quotes. Quotes are added around values that contain quotes or delimiters.
205 data _null_; 206 file log dsd ; 207 set sashelp.class(obs=3); 208 put (_all_) (+0); 209 run; Alfred,M,14,69,112.5 Alice,F,13,56.5,84 Barbara,F,13,65.3,98
If you add the ~ modifier then every value is quoted, whether it needs to be quoted or not.
211 data _null_; 212 file log dsd ; 213 set sashelp.class(obs=3); 214 put (_all_) (~); 215 run; "Alfred","M","14","69","112.5" "Alice","F","13","56.5","84" "Barbara","F","13","65.3","98"
Is it that your actual values have quotes and you want those transferred? Then they will need to doubled or else the system reading the file will remove them as just being there to protect the real value.
Post a simple example of a line or two of the data you want to export. Preferably in the form of a data step others could run to create the sample data. Also include what you want the generated file to look like for that sample data.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: