BookmarkSubscribeRSS Feed
Astounding
PROC Star

Can we assume that every CASE_NO has exactly one observation with a blank TFC, and at least one observation with a nonblank TFC?

 

In that case, here is an approach:

 

data want;

set have (where=(TFC=' ')) ;

output;

do until (last.case_no) ;

   set have (keep=case_no TFC DocType_A where=(TFC > ' ')) ;

   output;

end;

run;

Ant_K
Fluorite | Level 6

Hi Astounding,

 

Thanks for the reply and your suggestion. But i actually have in total of 52 variables for this table, and i only wish to copy the first 6 variable's data, so is there any other approach where i don't have to use the "keep" statement in line 5 to key in all the other 40+ variables that i want to keep?

Kurt_Bremser
Super User

@Ant_K wrote:

Hi Astounding,

 

Thanks for the reply and your suggestion. But i actually have in total of 52 variables for this table, and i only wish to copy the first 6 variable's data, so is there any other approach where i don't have to use the "keep" statement in line 5 to key in all the other 40+ variables that i want to keep?


Do it the other way around and use a drop dataset option.

Astounding
PROC Star

Sure.  Replace keep= with drop= and add the names of the 6 variables that you don't want to replace.

Tom
Super User Tom
Super User

You can probably use the UPDATE statement to do what you want, but you will need to first blank out the values that you don't want anymore.  Let's convert your second example into a dataset so we have something to program with.

data have ;
  length No 8 Acc_No $8 Agent $20 Src $1 
         Branch_F $2 Exec $2 Trx $2 DocType_A 8 TFC $1 
         Case_No $8 
  ;
  infile cards dsd dlm='|' truncover ;
  input no -- case_no ;
cards;
1|KU1234|Fairview Enterprise|A|KU|KU|KU|2| |ABC6345
2|KL4321|AMG Marine|X|KL|KL|KL|3|C|ABC6345
3|KL4321|AMG Marine|X|KL|KL|KL|4|C|ABC6345
4|KL4322|Macro Trust Fund |X|KL|KL|KL|5|C|ABC6345
5|SB9564|Essentials Dotcom|B|SB|SB|SB|1| |CBA2134
6|KL3214|Visual Optics|X|KL|KL|KL|7| |CBA2134
7|KL3214|Visual Optics|X|KL|KL|KL|8| |CBA2134
;

Now let's make a copy where some of the columns are set to missing when the value of TFC='C'.  I will use CALL MISSING() to do this and use a position based variable list.

data for_update ;
  set have ;
  if tfc='C' then call missing(of Acc_no -- Trx);
run;

Now we can use this new dataset as the transaction dataset in an UPDATE statement. For the master dataset we will use an empty dataset, which we can get by using the dataset option OBS=0.  Since you have multiple observations per CASE_NO we will need to add an OUTPUT statement to write an observation after each "transaction" is applied.

data want ;
  update have(obs=0) for_update ;
  by case_no;
  output;
run;

Here are the results.

image.png

And here is variable summary from PROC COMPARE.

image.png

Ant_K
Fluorite | Level 6
Dear Tom, Thanks for your solution, you are my saviour, i managed to solve my problem with your solution. Thanks again.

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
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
  • 22 replies
  • 6658 views
  • 2 likes
  • 4 in conversation