Hi Everyone,   I am trying to build a Hierarchy from the below file (source code title: code 1). The first level of the hierarchy should be CEO, and every subsequent level (represented by L2, L3 etc) should be correct.  One way of doing this is - Loop through all the Ls and identify the first L with a value of CEO. - The first L with a value of CEO will be Level 1, then the rest of the levels will automatically fall into place with Level 2 being Department Head, Level 3 being Senior manager etc. - Once Levels are defined as such, these could be appended to give a table like table 2 below;   Note: This is extremely simplified. Original table contains about 100k records         code 1:  data Ranking; INFILE DATALINES DLM=','; length CustID L1 $20 L2 $20 L3 $20 L4 $20 L5 $20 L6 $20; input CustID L1 $ L2 $ L3 $ L4 $ L5 $ L6 $; cards; 1,CEO,Department Head,Senior Manager,Manager, Senior Analyst, Peter 2,Major,Vice President, CEO, Department Head, Senior Manager,Manager 3,Technical Chief, CEO, Department Head, Senior Manager, Manager, Senior Analyst ;   The Finished product should look like this:   Can anyone think of a scripting that will get this result please?   Thanks,       ... View more

Hi Everyone,   A challenging one and apologies if its a bit of a read. Just want to give you as much information as possible to enable you figure out.   I have the following data sets (codes follow below);     Created with the below codes respectively: data Master_File; infile datalines dlm=','; length Survey_ref $8 Question1 $90; input Survey_ref $ Question1 $; cards; PPTV,How Likely are you to recommend [Staff_Name] from [Company_Name]? JJNT,How Likely are you to recomment [Company_Name] to a Friend? ; data Customer_Survey; infile datalines dlm=','; length Survey_ref $8 TQuestion1 $90 TStaff_Name $20 TCompany_Name $15; input Survey_ref $ TQuestion1 $ TStaff_Name $ TCompany_Name $; cards; PPTV, How Likely are you to recommend Alex Jefferson from Morrisons?,Alex Jefferson,Morrisons JJNT, How Likely are you to recommend Morrisons to a Friend?,Pablo, Morrisons ;   OBJECTIVE: I wish to compare survey questions on the different Files (Question1 Vs TQuestion1) for each survey_ref. To make this possible, go through the below steps; A. Try to Standardize both files e.g. by replacing the Staff_Name (value: Alex Jefferson) in TQuestion1 with [Staff_Name]  and also replace company name (Value: Morrisons) with [Company_Name] (These value substitutions are done in step C but writing it here gives a bit more context). Also, these value substitutions work  if I use actually values assigned to the Macro variables but doesn't work when i use the macro variables themselves. B. Merge both Files C. Compare values   A. data Master_File_simplified; set Master_File; /* Extract words in square brackets in Question 1*/ Inserted_word1 = scan(Question1,2,'[]'); Inserted_Word2 = scan(Question1,4,'[]'); /*Store Extracted words in Macro variables*/ call symput('Inserted_word1',Inserted_word1); call symput('Inserted_Word2',Inserted_Word2); run;   B. /*Merge Files - For simplicity, filtering for survey_ref='PPTV' only*/   data Mapped_file; merge Master_File_Simplified(in=M where=(survey_ref='PPTV')) Customer_Survey(in=T where=(survey_ref='PPTV')); by survey_ref; if M and T; run;   table from this is:   C.  data Final; set Mapped_file; if not missing(&Inserted_word1) then do; TTQuestion1=transtrn(TQuestion1,&Inserted_word1.,"[&Inserted_word1.]"); if not missing(&Inserted_Word2) then TTQuestion1=transtrn(TTQuestion1,&Inserted_word2.,"[&Inserted_word2.]"); end; run;   Error generated is:     How can i successfully make these comparisons without getting this error please. Mind you this is a simplification of the actual problem. The real problem has many different survey questions in that master File, with different sorts of hardcoded values in square brackets. ... View more