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

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.

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
  • 3 replies
  • 2231 views
  • 4 likes
  • 3 in conversation