DATA Step, Macro, Functions and more

Repeated values

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Repeated values

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


Accepted Solutions
Solution
‎09-06-2017 04:06 PM
PROC Star
Posts: 8,149

Re: Repeated values

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

 

View solution in original post


All Replies
Super User
Posts: 23,296

Re: Repeated values

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. 

Contributor
Posts: 21

Re: Repeated values

Thank you for you responce! Not sure how the APPL NAME will get reapeted on each line useing this methold.

Super User
Posts: 23,296

Re: Repeated values


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.

Contributor
Posts: 21

Re: Repeated values

I will continue to work through to gain a better understanding,  thank you!

Respected Advisor
Posts: 3,167

Re: Repeated values

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;
Solution
‎09-06-2017 04:06 PM
PROC Star
Posts: 8,149

Re: Repeated values

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

 

PROC Star
Posts: 1,584

Re: Repeated values

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;

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 222 views
  • 4 likes
  • 5 in conversation