No spaces in csv output

[PWS]

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 ;

[Tom]

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);

[PWS]

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

[Tom]

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.

[Tom]

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.