BookmarkSubscribeRSS Feed
etl_tool
Calcite | Level 5

Hi,

I'm trying to send out ID's of a SAS dataset in  an email but not able to get the format right. I just need plain text as html is getting stuck and slow. Thanks in advance to help ! Any one solution would be good.

I have one ID column. The first solution one gives a complete list like

First

%include "/saswrk/go/scripts/envsetup.sas";

filename mymail email "&emaillist."

subject="  &env. Records Transferred on %sysfunc(date(),yymmdd10.)";

    data null;

      set WORKGO.recds_processed;

      file mymail;

    put (_all_)(=);

   run; quit;

Output

ID=1

ID=2

ID=3

ID=4

ID=5

It would be nice if i could get the count and output like 

Number of records processed=6 and the ID's are


ID=1,ID=2,ID=3.. (Separated by Commas)

Second Solution


filename eml email to='some@work.com';

proc printto print=eml;

proc sql;

select ID from WORKGO.recds_processed;

quit;

proc printto;

The second one gives

ID

1

2

3

SAS System

4

5

6

SAS system

It would be good if I can get the count written in the body and have just one column continuously instead of the SAS system word breaking it after some number something like this

The number of records processed are 6 and the ID's are

ID

1

2

3

4

5

6

and so on ..

2 REPLIES 2
ledfoot
SAS Employee

There has got to be a  hundred ways to do this but this is the hardest most obscure way I could think of. The code is incomplete and poorly written, but it runs for me...YOYO

   Data WORK.recds_processed;

   Length ID $ 6;

    ID="1";

    output;

    ID="2";

    output;

    ID="3";

    output;

    ID="4";

    output;

    ID="5";

    output;

  ID="6";

    output;

    ID="7";

    output;

    ID="8";

    output;

    ID="9";

    output;

    ID="10";

    output;

    ID="11";

    output;

  ID="12";

    output;

  ID="13";

    output;

    ID="14";

    output;

    ID="15";

    output;

    ID="16";

    output;

    ID="17";

    output;

  ID="18";

    output;

    ID="1911";

    output;

    ID="2011";

    output;

    ID="21";

    output;

    ID="22";

    output;

    ID="23";

    output;

  ID="24";

    output;

   Run;

   %let outfile = idList.txt;

   %let outdir = D:\All_Users\sasled\temp;

   /* get the count */

   Data _NULL_;

   Set WORK.recds_processed end=lastobs;

      length strcnt $ 20;

      retain cnt 0;

   

      if not Missing( ID ) then cnt=cnt+1;

   

      if (lastobs) then do;

         /* save cnt */

         strcnt = CATS(cnt);

         call symput('ID_Cnt', strcnt);

      end;

   Run;

/* write out the ID info */

   Data _NULL_;

   Set WORK.recds_processed end=lastobs;

   %LET linemax= 80;

   length idstr $ 25;

   length outlinebuf $ &linemax;

  

   retain row1 1;

   retain outlen 0;

   retain outlinebuf;

   retain fid;

   if ( row1 = 1 ) then do; /* open output file */

      row1=0;

      /* set text file filename */

   outf = CATS("&outdir",'\',"&outfile");

      put 'Output file name = ' outf;

   put ;

      rc = Filename("otxt",outf);

      if rc ne 0 then do;

         msg = sysmsg();

         put msg;

         call symput('SYSCC','8');

         goto Term;

      end;

      fid = fopen("otxt",'o',,'d');

      if (fid <= 0) then do;

          /* ERROR: Open failed for file, &outdir\&outfile.*/

             emsg=sysmsg();

             put "ERROR: " emsg;

             call symput('SYSCC','8');

             goto Term;

      end;

      /* output count summary */

      outlinebuf = CAT('Number of records processed= ',&ID_Cnt,' the IDs are:');

      rc=fput(fid,outlinebuf);

      rc=fwrite(fid);

      outlinebuf ='                                                                               ';      

   end;

   idstr = CAT("ID = ",ID);

   idlen = LENGTHN(idstr);

   if (outlen + idlen + 1) >= &linemax then do;

      /* output line on length..*/

      rc=fput(fid, outlinebuf);

   rc=fwrite(fid);

   outlen = 0;

   outlinebuf ='                                                                               ';      

   end;

    

   outlinebuf = CATX(', ',outlinebuf, idstr);

   /* get new buf len */ 

   outlen = LENGTHN(outlinebuf);

   if (lastobs) then do;

      rc=fput(fid, outlinebuf);

      rc=fwrite(fid);

      put " Closing output file.....";

      rc = fclose(fid);

   end;

   Term:;

   Run;

etl_tool
Calcite | Level 5

Thanks ledfoot, But here's what Alex from stackoverflow suggested it breaks the ID sometimes when it hits the 3000 length because I have more than 60000 records which I do not know how to resolve and also there is another dataset workgo.recs_not_processed which I have to print in the same way in the same email. The output is breaking the ID's which should be avoided and below this I should add recs not processed= and the id's not processed=

recs processed=60

awd_id's 123456,456789,123890,123

890,453212,555555,9231

43

%include '/saswrk/go/scripts/envsetup.sas';

filename mymail email
"&emaillist"
  subject
= "&env Records Transferred on %sysfunc(date(), yymmdd10.)";

data _null_
;
  length id_list
$ 3000;
  retain id_list
'';

  
set workgo.recds_processed nobs = nobs end = eof;
  
file mymail;

  
if _n_ = 1 then do;
  put
'Number of records processed=' nobs;
  put
'The IDs are:';
  
end;

  
/* Print the IDs in chunks */
  
if length(strip(id_list)) > 2000 then do;
  put id_list
;
  call missing
(id_list);
  
end;

  call catx
(', ', id_list, id);

  
if eof then put id_list;
run
;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1359 views
  • 0 likes
  • 2 in conversation