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

Hi Team,

Please assist in getting the desired output mentioned in the attached excel sheet

There are two tables.  The output should be like the right table (Yellow in colour).

Kind Regards

SHiva

1 ACCEPTED SOLUTION

Accepted Solutions
boin
Obsidian | Level 7

Amazing , thanks Richard  it works....Smiley Happy

View solution in original post

5 REPLIES 5
RichardinOz
Quartz | Level 8

Shiva

You need to explain the business logic behind the transformation you want.

Why are certain columns repeated in the yellow data?

Why are some cells set to null?

Is there any row level relationship between the apparent director-director, GM-GM and manager-manager entries, and if so, what is the logic?

Richard

Peter_C
Rhodochrosite | Level 12

Looks like a 2 stage exercise:

  1. Taking advantage of the ordered input, DOW loops nested for each by-group level would simplfy the extract of the boss-underling pairs (just the relevant columns, controlled by keep lists as dataset options)  into separate streams of data which could be written to a separate table for each
  2. A merge with no BY statement will place the separate streams (datasets) side-by-side.

If there might be situations where an individual had more than one boss, the streams should be sorted (NODUPKEY)

RichardinOz
Quartz | Level 8

On closer inspection I agree with Peter's analysis that the yellow consists of 4 separate streams, each of which is the unique relationship between adjacent levels of this (surely bogus!) hierarchy.  However, to preserve the original order the data needs to be analysed using the notsorted option.  Here is the code for the first 2 cols

data cols_IJ ;

    set have ;

    by notsorted Director ;

    keep ExDirector Director ;

    if first.director ;

run ;

Richard

boin
Obsidian | Level 7

Amazing , thanks Richard  it works....Smiley Happy

boin
Obsidian | Level 7

Thanks Peter for the steps ...Smiley Happy

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!

How to Concatenate Values

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.

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
  • 5 replies
  • 842 views
  • 8 likes
  • 3 in conversation