topic How to properly MERGE shell/dummy with data in SAS Programming

How to properly MERGE shell/dummy with data

HitmonTran — Tue, 09 Jun 2020 21:36:08 GMT

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;

Re: How to properly MERGE shell/dummy with data

ballardw — Wed, 10 Jun 2020 00:53:20 GMT

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.

Re: How to properly MERGE shell/dummy with data

smantha — Wed, 10 Jun 2020 02:14:38 GMT

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;

Re: How to properly MERGE shell/dummy with data

ChrisNZ — Wed, 10 Jun 2020 03:19:07 GMT

Like this?

if A | VAL ;

Re: How to properly MERGE shell/dummy with data

ChrisNZ — Wed, 10 Jun 2020 03:21:31 GMT

Even better:

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

Re: How to properly MERGE shell/dummy with data

HitmonTran — Wed, 10 Jun 2020 04:43:25 GMT

Hi Smantha,

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