BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
EEEY
Obsidian | Level 7

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!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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

3 REPLIES 3
Patrick
Opal | Level 21

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...

PGStats
Opal | Level 21
Typo, at the end: If LAST.company then do;
PG
EEEY
Obsidian | Level 7
Thank you so much for the simple, efficient solution.. Love it...

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

SAS Enterprise Guide vs. SAS Studio

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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