Combination of two data sets

[Shuail_Ibrahim]

Hi there,

I wonder if someone can help me.

As I'm new to SAS please help me with the code to achieve the below output. 

[PaigeMiller]

Are you using code, or doing this in Enterprise Guide or Enterprise Miner via the GUI?

If you can provide the input data as actual text, instead of screen captures, I can try to provide working code. Otherwise, I would turn data set 2 into custom formats, and then apply these to data set 1. (In other words, it's not a "combination" of the two data sets, it's using data set 2 to change the appearance of data set 1.)

[Shuail_Ibrahim]

Paige Miller Please can u share the code for dataset 2 to change the appearance of data set1

[PaigeMiller]

As I said, I need you to provide the data for both data sets as text, not screen captures.

[Shuail_Ibrahim]

Please find the below data sets
emp_id TEST1 TEST2 TEST3 TEST4
10850 0 1 0 1
10851 1 0 0 0
10852 0 0 1 0
10853 0 0 0 0

GROUP CODE
TEST1 85XX123
TEST2 85XX196
TEST3 85XX125
TEST4 85XX078

[andreas_lds]

---

@Shuail_Ibrahim wrote:
Please find the below data sets
emp_id TEST1 TEST2 TEST3 TEST4
10850 0 1 0 1
10851 1 0 0 0
10852 0 0 1 0
10853 0 0 0 0

GROUP CODE
TEST1 85XX123
TEST2 85XX196
TEST3 85XX125
TEST4 85XX078

---

No, you haven't posted dataset but lines of text, the reason why @PaigeMiller asked for datasets is, that we are using the same variable-types and lengths that you have in your datasets. Another point: please don't double post question, https://communities.sas.com/t5/Developers/How-to-replace-value-with-Another-Row-value-in-SAS-from-Da... seems to be about the same topic.

[PaigeMiller · Accepted solution]

data two;
	retain type 'n';
    input GROUP $ CODE $;
	fmtname=cats(trim(group)||'f');
	start=1; 
	label=code;
	cards;
TEST1 85XX123
TEST2 85XX196
TEST3 85XX125
TEST4 85XX078
;

proc format cntlin=two;
run;

data one;
input emp_id TEST1 TEST2 TEST3 TEST4;
format test1 test1f. test2 test2f. test3 test3f. test4 test4f.;
cards;
10850 0 1 0 1
10851 1 0 0 0
10852 0 0 1 0
10853 0 0 0 0
;

[Shuail_Ibrahim]

thanks a lot, Paige miller the code works out exactly as the result set. instead of cards, how to import data from sas7bdat???

[PaigeMiller]

---

@Shuail_Ibrahim wrote:
thanks a lot, Paige miller the code works out exactly as the result set. instead of cards, how to import data from sas7bdat???

---

UNTESTED CODE

data two_a;
     set dataset2;
    retain type 'n';
	fmtname=cats(trim(group)||'f');
	start=1; 
	label=code;
run;

proc format cntlin=two;
run;

data one_a;
    set dataset1;
    format test1 test1f. test2 test2f. test3 test3f. test4 test4f.;
run;

[Shuail_Ibrahim]

Thanks a lot, Paige Miller for spending your valuable time, it really helped a lot ...

[Shuail_Ibrahim]

Hi, Paige Miller finally I need a small help from you. if the columns are fixed like test, test1 the above set of code works . can u please help me when having n number of columns test, test1, ......test n. it would be great if u can just help me out in this

[PaigeMiller]

So, first let me comment for your future reference — if you want a solution that is going to apply when the number of columns is 309 rather than a solution when number of columns is 4, you should mention that, and then we won't waste our time giving you a solution when the number of columns is 4.

UNTESTED CODE

data two_a;
     set dataset2;
     retain type 'n';
	fmtname=cats(trim(group)||'f');
	start=1; 
        end=1;
	label=code;
run;

proc format cntlin=two;
run;

proc sql noprint;
    select trim(group)||' '||trim(group)||'f.' into :formats separated by ' ' from dataset2;
quit;
     
data one_a;
    set dataset1;
    format &formats;
run;

[Kurt_Bremser]

Your data does not look like SAS datasets at all, rather like screenshots of Excel spreadsheets. That also explains your bad data modeling, keeping data (group values) in structure (variable names).

As a first step, you need to transpose your dataset to an intelligent long format, which makes coding much easier:

data have;
input emp_id $ TEST1 TEST2 TEST3 TEST4;
datalines;
10850 0 1 0 1
10851 1 0 0 0
10852 0 0 1 0
10853 0 0 0 0
;

data groups;
input group $ code $;
datalines;
TEST1 85XX123
TEST2 85XX196
TEST3 85XX125
TEST4 85XX078
;

proc transpose data=have out=trans (rename=(col1=test)) name=group;
by emp_id;
var test:;
run;

data want;
set trans;
if _n_ = 1
then do;
  length code $8;
  declare hash gr (dataset:"groups");
  gr.definekey('group');
  gr.definedata('code');
  gr.definedone();
end;
if test = 1
then gr.find();
else code = '0';
drop test;
run;

By deleting the observations without a TEST value of 1 (and subsequent setting of CODE), you can greatly reduce the required storage.