Desktop productivity for business analysts and programmers

How to output a dataset into Text file with multiple header and trailer?

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

How to output a dataset into Text file with multiple header and trailer?

The dataset test I have like:

companyemplidname
abc0123Joe
bcd1111Eric
abc2222Mary
aaa3333Mike
aaa4444Sarah
aaa5555Ellen

 

 

Basically in the text file, I want to see by each company, how many employees do they have for each company and add the Header and trailer. The header that would be 'E'||company. The content would be 'W'||emplid name. The Trailer would be 'T'||nbr of emplids for that company.

 

I'd like to output the dataset into one text file by company. the Text file I am trying to get would be looking ike:

 

Eaaa
W3333 Mike
W4444 Sarah
W5555 Ellen
T0003
Eabc
W0123 Joe
W2222 Mary
T0002
Ebcd
W1111 Eric
T0001

 

Ionly know how to create a text file for each company like the code below but do not know how to concatenate differnt text file together into one text file. Or maybe with other technique that I can export to txt file with the layout I am looking for.

%let com=aaa;

proc sql;

select PUT(COUNT(DISTINCT EMPLID), Z5.) INTO :CNT_&COM

FROM test

where company=&com;

QUIT;

 

 DATA _NULL_;

SET test end=last;

if company='aaa';

File ".../new_&com..txt" lrecl=30;

TR=trim("T"||"&&&CNT_&COM.");

if _n_=1 then do;

put @1 'E'

@2 company

;

end;

PUT  

@1 'W'

@2 emplid

@5 name; 

end;

if last then do;

 PUT TR;

end;

run;

 

Please help!

 

 

 


Accepted Solutions
Solution
‎10-13-2015 02:18 PM
Respected Advisor
Posts: 4,132

Re: How to output a dataset into Text file with multiple header and trailer?

[ Edited ]

Something like below should do.

data have;
  input company $ emplid name $;
  datalines;
abc 0123 Joe
bcd 1111 Eric
abc 2222 Mary
aaa 3333 Mike
aaa 4444 Sarah
aaa 5555 Ellen
run;
proc sort data=have;
  by company;
run;

data _null_;
  file print;
  set have;
  by company;
  if first.company then
    do;
      put 'E' company;
    end;
  put emplid name;
  count+1;
  if last.company then
    do;
      put 'T' count z4.;
      call missing(count);
    end;
run;

 

Fixed as per @PGStats post. Starts to become a pattern...

View solution in original post


All Replies
Solution
‎10-13-2015 02:18 PM
Respected Advisor
Posts: 4,132

Re: How to output a dataset into Text file with multiple header and trailer?

[ Edited ]

Something like below should do.

data have;
  input company $ emplid name $;
  datalines;
abc 0123 Joe
bcd 1111 Eric
abc 2222 Mary
aaa 3333 Mike
aaa 4444 Sarah
aaa 5555 Ellen
run;
proc sort data=have;
  by company;
run;

data _null_;
  file print;
  set have;
  by company;
  if first.company then
    do;
      put 'E' company;
    end;
  put emplid name;
  count+1;
  if last.company then
    do;
      put 'T' count z4.;
      call missing(count);
    end;
run;

 

Fixed as per @PGStats post. Starts to become a pattern...

Respected Advisor
Posts: 4,814

Re: How to output a dataset into Text file with multiple header and trailer?

Typo, at the end: If LAST.company then do;
PG
Contributor
Posts: 26

Re: How to output a dataset into Text file with multiple header and trailer?

Thank you so much for the simple, efficient solution.. Love it...
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 658 views
  • 4 likes
  • 3 in conversation