topic Re: Join ? in New SAS User

Join ?

Guess_ — Fri, 16 Apr 2021 09:07:13 GMT

Hi all,

suppose that i have a table 1 with DAY ( all Year 2020)and value for x and y for whom I interested to.

date x y

01/02/2020 2 4

02/02/2020 3 4

and table 2 that reported a range of DAY from 2010 to 2020 and a number of value> then table 1

date a x y z

01/02/2010 1 -

02/02/2010 1 -

01/02/2020 - 66 55 10

I would like to have a Table 3 that match on date (only 2020) in order to obtain:

01/02/2020 - 2 4 10

01/02/2020 - 3 4 10

So, i need to compare the two table and add a column in table two in case of which z is missing in table 1 but presente in 2020.

How can perform it in SAS?

Re: Join ?

Kurt_Bremser — Fri, 16 Apr 2021 11:36:13 GMT

Do you have multiple values for a single date in dataset 2, or is that a typo?

Re: Join ?

Guess_ — Fri, 16 Apr 2021 12:17:42 GMT

I'm interested to have a table that cointains all variable (a,x,y,z...) in a specific dates range ( example from 01/01/2020 to 31/01/2020).

The problem is that merging Tab 1 and Tab 2 I obtain also the variable like "a" (in my example) which have missing value for ALL days in 2020,

Does it possible delete a column without specify the name of Variable "a" but delete under conditions?

I mean, if in tab3 there are variables that have "." for ALL days into 2020, which is the method to delete they?

Consider that my Tab3 is built by 250 variable, so specify conditon for each of them it is quite time expensive and furtheremore if the number of variable increase I should add it in my query.

Thank you for your help

Re: Join ?

Guess_ — Fri, 16 Apr 2021 12:19:28 GMT

I forgot, I don't have multiple values for the dates.

I have a column with 365 days and the corresponded observations for each variables.

Re: Join ?

Kurt_Bremser — Tue, 20 Apr 2021 13:17:45 GMT

You can check for all-missing variables like that:

data have;
input a b c;
datalines;
1 . .
3 . .
5 . .
;

proc summary data=have;
var _numeric_;
output out=max (drop=_type_ _freq_) max()=;
run;

proc transpose data=max out=vars (where=(col1 = .));
var _all_;
run;

proc sql noprint;
select _name_ into :dropvars separated by " "
from vars;
quit;

%put &=dropvars;

and use the macro variable in a DROP statement or DROP= dataset option.