I have a list of jobs that report in the following format
COLS 1 – 40 LONGNAME
COLS 41 – 55 FIELD NAME
COLS 56 – 76 DETAILS
APPL NAME is always the 4th filed in the display. I need the DETAILS of APPL NAME on every line. The problem I am running into is the APPL NAME is the 4th item in the list, and I need maintain the order.
DATA I HAVE
“LONGANME” “FIELD” “DETAILS”
ENDOFDAY.FTRG AGENT:
ENDOFDAY.FTRG ANYCLOSE:
ENDOFDAY.FTRG APPL DESCR:
ENDOFDAY.FTRG APPL NAME: ADMGC35
ENDOFDAY.FTRG ARGS:
ENDOFDAY.FTRG CALENDAR(S): FISCAL
ENDOFDAY.FTRG CLASS:
ENDOFDAY.FTRG CMDNAME:
ENDOFDAY.FTRG CONTINUOUS:
ENDOFDAY.FTRG COUNT:
ENDOFDAY.FTRG CPU: 0H 0M 0S
ENDOFDAY.FTRG DELAYSUB: 10:59
ENDOFDAY.FTRG DELETE:
ENDOFDAY.FTRG DIS RESTART:
ENDOFDAY.FTRG DIST FOLDER:
ENDOFDAY.FTRG DIST ROLES:
ENDOFDAY.FTRG DIST USERS:
ENDOFDAY.FTRG DOCLIB: SYS77.ESP.DOCLIB
ENDOFDAY.FTRG AGENT:
ENDOFDAY.FTRG ANYCLOSE:
ENDOFDAY.FTRG APPL DESCR:
ENDOFDAY.FTRG APPL NAME: ADMGC36
ENDOFDAY.FTRG ARGS:
ENDOFDAY.FTRG CALENDAR(S): FISCAL
ENDOFDAY.FTRG CLASS:
ENDOFDAY.FTRG CMDNAME:
ENDOFDAY.FTRG CONTINUOUS:
ENDOFDAY.FTRG COUNT:
ENDOFDAY.FTRG CPU: 0H 0M 0S
ENDOFDAY.FTRG DELAYSUB: 10:59
ENDOFDAY.FTRG DELETE:
ENDOFDAY.FTRG DIS RESTART:
ENDOFDAY.FTRG DIST FOLDER:
ENDOFDAY.FTRG DIST ROLES:
ENDOFDAY.FTRG DIST USERS:
ENDOFDAY.FTRG DOCLIB: SYS77.ESP.DOCLIB
DATA I WANT
COLS 1 – 40 LONGNAME
COLS 41 – 55 FIELD NAME
COLS 56 – 76 DETAILS
COLS 77 - 83 APPL NAME
“LONGANME” “FIELD” “DETAILS” APPL NAME”
ENDOFDAY.FTRG AGENT: ADMGC35
ENDOFDAY.FTRG ANYCLOSE: ADMGC35
ENDOFDAY.FTRG APPL DESCR: ADMGC35
ENDOFDAY.FTRG APPL NAME: ADMGC35 ADMGC35
ENDOFDAY.FTRG ARGS: ADMGC35
ENDOFDAY.FTRG CALENDAR(S): FISCAL ADMGC35
ENDOFDAY.FTRG CLASS: ADMGC35
ENDOFDAY.FTRG CMDNAME: ADMGC35
ENDOFDAY.FTRG CONTINUOUS: ADMGC35
ENDOFDAY.FTRG COUNT: ADMGC35
ENDOFDAY.FTRG CPU: 0H 0M 0S ADMGC35
ENDOFDAY.FTRG DELAYSUB: 10:59 ADMGC35
ENDOFDAY.FTRG DELETE: ADMGC35
ENDOFDAY.FTRG DIS RESTART: ADMGC35
ENDOFDAY.FTRG DIST FOLDER: ADMGC35
ENDOFDAY.FTRG DIST ROLES: ADMGC35
ENDOFDAY.FTRG DIST USERS: ADMGC35
ENDOFDAY.FTRG DOCLIB: DOCLIB ADMGC35
ENDOFDAY.FTRG AGENT: ADMGC36
ENDOFDAY.FTRG ANYCLOSE: ADMGC36
ENDOFDAY.FTRG APPL DESCR: ADMGC36
ENDOFDAY.FTRG APPL NAME: ADMGC36 ADMGC36
ENDOFDAY.FTRG ARGS: ADMGC36
ENDOFDAY.FTRG CALENDAR(S): FISCAL ADMGC36
ENDOFDAY.FTRG CLASS: ADMGC36
ENDOFDAY.FTRG CMDNAME: ADMGC36
ENDOFDAY.FTRG CONTINUOUS: ADMGC36
ENDOFDAY.FTRG COUNT: ADMGC36
ENDOFDAY.FTRG CPU: 0H 0M 0S ADMGC36
ENDOFDAY.FTRG DELAYSUB: 10:59 ADMGC36
ENDOFDAY.FTRG DELETE: ADMGC36
ENDOFDAY.FTRG DIS RESTART: ADMGC36
ENDOFDAY.FTRG DIST FOLDER: ADMGC36
ENDOFDAY.FTRG DIST ROLES: ADMGC36
ENDOFDAY.FTRG DIST USERS: ADMGC36
ENDOFDAY.FTRG DOCLIB: DOCLIB ADMGC36
Here is another alternative:
data want; set have; length appl_name $10; retain appl_name; if field eq "AGENT:" then do; loc=_n_+3; set have (keep=details rename=(details=appl_name)) point=loc; end; run;
Art, CEO, AnalystFinder.com
Is AGENT always the 'record starter' than needs to get filled?
Simplest fix:
1. Create a group id that increments every time a new AGENT field is identified.
2. Create a subset with teh GROUP and APPL name
3. Merge back into the results.
Thank you for you responce! Not sure how the APPL NAME will get reapeted on each line useing this methold.
@a079011 wrote:
Thank you for you responce! Not sure how the APPL NAME will get reapeted on each line useing this methold.
Try it first, then follow the code through to see if you understand it. Try adding comments to each line to clarify your understanding.
I will continue to work through to gain a better understanding, thank you!
A little bit confused here. Are you trying to get a output in Text? as those column positions are not often used under SAS data settings. @Reeza is pointing you to the right direction if you are talking about SAS data. Given your data as is, the below is an alternative.
data have;
/*infile cards ;*/
input LONGNAME $ 1-38 FIELD $ 39-54 DETAILS $ 55-76;
cards;
ENDOFDAY.FTRG AGENT:
ENDOFDAY.FTRG ANYCLOSE:
ENDOFDAY.FTRG APPL DESCR:
ENDOFDAY.FTRG APPL NAME: ADMGC35
ENDOFDAY.FTRG ARGS:
ENDOFDAY.FTRG CALENDAR(S): FISCAL
ENDOFDAY.FTRG CLASS:
ENDOFDAY.FTRG CMDNAME:
ENDOFDAY.FTRG CONTINUOUS:
ENDOFDAY.FTRG COUNT:
ENDOFDAY.FTRG CPU: 0H 0M 0S
ENDOFDAY.FTRG DELAYSUB: 10:59
ENDOFDAY.FTRG DELETE:
ENDOFDAY.FTRG DIS RESTART:
ENDOFDAY.FTRG DIST FOLDER:
ENDOFDAY.FTRG DIST ROLES:
ENDOFDAY.FTRG DIST USERS:
ENDOFDAY.FTRG DOCLIB: SYS77.ESP.DOCLIB
ENDOFDAY.FTRG AGENT:
ENDOFDAY.FTRG ANYCLOSE:
ENDOFDAY.FTRG APPL DESCR:
ENDOFDAY.FTRG APPL NAME: ADMGC36
ENDOFDAY.FTRG ARGS:
ENDOFDAY.FTRG CALENDAR(S): FISCAL
ENDOFDAY.FTRG CLASS:
ENDOFDAY.FTRG CMDNAME:
ENDOFDAY.FTRG CONTINUOUS:
ENDOFDAY.FTRG COUNT:
ENDOFDAY.FTRG CPU: 0H 0M 0S
ENDOFDAY.FTRG DELAYSUB: 10:59
ENDOFDAY.FTRG DELETE:
ENDOFDAY.FTRG DIS RESTART:
ENDOFDAY.FTRG DIST FOLDER:
ENDOFDAY.FTRG DIST ROLES:
ENDOFDAY.FTRG DIST USERS:
ENDOFDAY.FTRG DOCLIB: SYS77.ESP.DOCLIB
;
run;
FILENAME _OUT "/yourfolder/WANT.TXT";
data _NULL_;
LENGTH
APPL_NAME $ 100 ;
do until (field='DOCLIB:');
set HAVE;
if field=:'APPL NAME' THEN APPL_NAME=DETAILS;
END;
do until (field='DOCLIB:');
set HAVE;
FILE _OUT ;
PUT
LONGNAME 1-40 FIELD 41-55 DETAILS 56-76
APPL_NAME 77-83;
END;
RUN;
FILENAME _OUT CLEAR;
Here is another alternative:
data want; set have; length appl_name $10; retain appl_name; if field eq "AGENT:" then do; loc=_n_+3; set have (keep=details rename=(details=appl_name)) point=loc; end; run;
Art, CEO, AnalystFinder.com
data have;
infile datalines truncover;
input LONGANME : $15. FIELD & $10. DETAILS & $10.;
datalines;
ENDOFDAY.FTRG AGENT:
ENDOFDAY.FTRG ANYCLOSE:
ENDOFDAY.FTRG APPL DESCR:
ENDOFDAY.FTRG APPL NAME: ADMGC35
ENDOFDAY.FTRG ARGS:
ENDOFDAY.FTRG CALENDAR(S): FISCAL
ENDOFDAY.FTRG CLASS:
ENDOFDAY.FTRG CMDNAME:
ENDOFDAY.FTRG CONTINUOUS:
ENDOFDAY.FTRG COUNT:
ENDOFDAY.FTRG CPU: 0H 0M 0S
ENDOFDAY.FTRG DELAYSUB: 10:59
ENDOFDAY.FTRG DELETE:
ENDOFDAY.FTRG DIS RESTART:
ENDOFDAY.FTRG DIST FOLDER:
ENDOFDAY.FTRG DIST ROLES:
ENDOFDAY.FTRG DIST USERS:
ENDOFDAY.FTRG DOCLIB: SYS77.ESP.DOCLIB
ENDOFDAY.FTRG AGENT:
ENDOFDAY.FTRG ANYCLOSE:
ENDOFDAY.FTRG APPL DESCR:
ENDOFDAY.FTRG APPL NAME: ADMGC36
ENDOFDAY.FTRG ARGS:
ENDOFDAY.FTRG CALENDAR(S): FISCAL
ENDOFDAY.FTRG CLASS:
ENDOFDAY.FTRG CMDNAME:
ENDOFDAY.FTRG CONTINUOUS:
ENDOFDAY.FTRG COUNT:
ENDOFDAY.FTRG CPU: 0H 0M 0S
ENDOFDAY.FTRG DELAYSUB: 10:59
ENDOFDAY.FTRG DELETE:
ENDOFDAY.FTRG DIS RESTART:
ENDOFDAY.FTRG DIST FOLDER:
ENDOFDAY.FTRG DIST ROLES:
ENDOFDAY.FTRG DIST USERS:
ENDOFDAY.FTRG DOCLIB: SYS77.ESP.DOCLIB
;
data want;
if _N_ = 1 then do;
length _details $50;
Call missing(_details);
declare hash h( dataset:"have(rename=(details=_details) where=(FIELD='APPL NAME:') )",multidata:'yes');
declare hiter iter('h');
h.defineKey('field');
h.defineData('_details');
h.defineDone();
end;
set have;
retain APPL_NAME ;
if _n_=1 and field='AGENT:' then do;
iter.first();
APPL_NAME=_details;
end;
else if field='AGENT:' then do;
iter.next();
APPL_NAME=_details;
end;
drop _:;
run;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.