How to properly MERGE shell/dummy with data

[HitmonTran]

Hi,

I am merging shell/dummy dataset with a dataset.  How to output only those with data and keep the shell/dummy labels as well?

Hopefully I give you guys enough information

Current output:

Yellow highlight = from table shell w/data (Need to Keep the table lables)

Red X = from table shell w/ No data (need to remove everything)

HIghlight yellow= from table shell, need to keep. Red "X" = from table shell w/ empty data, DO NOT output;

code: 

data all;
	merge shell		(in=a) 
		  allstats	(in=b) ;

	by ord1 ord2 ord3;
	if  a ; /*if b;*/
run;

[ballardw]

I might try

data all;
	merge shell		(in=a) 
		  allstats	(in=b) ;
	by ord1 ord2 ord3;
	if first.ord2 or in b;
run;

Each variable on your by statement has some associated temporary variables that indicate whether the current record is the first or last of the current value of the variable. You access them by First.variable or Last.variable, note the dot in there. These temporary variables are 1/0 numeric values and SAS treats 1 as true and 0 as false.

It is not clear to  me if there are supposed to be two headers at the top or not. You may have to add in a test for the value of: or DESC='Baseline' into the IF. 

[smantha]

1. Making an assumption that if one of the statistic is missing then all values are missing. E.g if n is missing for ord2 then all values are missing for ord2 such as min max, sd etc.
After doing the merge do the following:
Proc sql;
Create table drop_ as
Select distinct ord2 into from merged_data where missing(var) and var_name = ‘n’;
Create table merged_datA as select * from merged_data where ord2 not in (
Select ord2 from droP_);
Quit;

[HitmonTran]

Hi Smantha,

it is giving me an error message on "into" statement.

[ChrisNZ]

Like this?

if A | VAL ;

[ChrisNZ]

Even better:

data ALL;
  merge SHELL    
        ALLSTARTS (where=(VAL)) ;
  by ORD1 ORD2 ORD3;
run;