topic Re: Columns are missing while merging in SAS Data Management

Columns are missing while merging

NareshAbburi — Mon, 03 Nov 2014 20:10:07 GMT

hi,

I've two tables which needs to be merged. While doing so, I'm being left with only few columns in the resultant table.

Why so....?

data logfile_final_UI7;

merge logfile_new new_parse;

end;

Please help me

Re: Columns are missing while merging

ballardw — Mon, 03 Nov 2014 20:15:05 GMT

How about some details such as:

Variables in logfile_new

Variable in new_parse

And the missing columns.

A few rows of data from each file and the result would also be helpful.

Re: Columns are missing while merging

NareshAbburi — Mon, 03 Nov 2014 20:18:28 GMT

Please find the attached.

Re: Columns are missing while merging

NareshAbburi — Mon, 03 Nov 2014 20:19:27 GMT

Re: Columns are missing while merging

ballardw — Mon, 03 Nov 2014 20:31:34 GMT

You haven't said which column is missing or provided an example of the actual output.

And the second set of code references two different data sets.

Do the two datasets have the same number of rows? Without a by statement to line things up then you may be getting missing results because of the numbers of rows not matching.

And instead of that many If then else statements:

array a Action1-action40;

action_count=0;

do j = dim(a) to 1;

if a ne ' ' then action_count=j;

if action_count > 0 then leave;

end;

Re: Columns are missing while merging

NareshAbburi — Mon, 03 Nov 2014 20:45:36 GMT

hi,

this is the output table. your answer helped to make an array instead of big query.

but the missing column is FILEID.

Re: Columns are missing while merging

Reeza — Mon, 03 Nov 2014 21:25:07 GMT

Your code looks incorrect. I would expect a merge to have a BY statement.

data logfile_final_UI7;

merge logfile_new new_parse;

BY fileid;

****other sas code here***;

run;

Re: Columns are missing while merging

ballardw — Mon, 03 Nov 2014 21:47:29 GMT

Run proc contents on the two input files and the result files and post that information. Screen shots of data sets aren't that helpful as they don't show all of the columns.

Re: Columns are missing while merging

LinusH — Wed, 05 Nov 2014 18:57:34 GMT

I haven't dug into you problem, but your code clearly shouts: Transpose My Data Set!

Re: Columns are missing while merging

RamKumar — Thu, 06 Nov 2014 13:17:21 GMT

What needs to be done if I need to create a new variable based on looping?

if a ne ' ' then action_count=j;

Re: Columns are missing while merging

RamKumar — Sat, 08 Nov 2014 16:26:17 GMT

any suggestions?

Re: Columns are missing while merging

Patrick — Sat, 08 Nov 2014 23:53:15 GMT

pointed already out that you need a "by" statement for your merge and I totally agree with that having a long instead of a wide data structure is very often beneficial.

Under the assumption that your Action1 to Action40 variables are populated starting with Action1 and that once there is a missing all later Action variables are also missing, then one could simply count the number of non-missing variables. That's what's done in below code.

data have(drop=_i);

length rowid 8;

array action {40} 8.;

do rowid=1 to 20;

do _i=1 to dim(action);

action[_i]=1;

if ceil(ranuni(1)*40)=20 then leave;

end;

output;

call missing(of action

);

end;

stop;

run;

data want;

length rowid action_count 8;

set have;

array action {*} action1-action40;

action_count=n(of action

);

run;