merging

[Dhana18]

Hi,

I have a data set with 25000 tests records but they are 12681 unique patient visits.

I have spec Id that is unique to each test, spec collect date that repeats because same patient can have multiple test on the same date. and patient id is also repeated. other variables I have are test type (only two categories) and result.

I also created an unique id from dob and visit date.

UNIQUE_ID	SPEC_COLLECT_DATE	SPEC_ID	LAB_PATIENTID	SPEC_SOURCE	TESTTYPE1	RESULT1
3513742828	4/3/2018	P6030025	21501	UR	2	0
3513742828	4/3/2018	P6030026	21501	U	1	1
1973542828	4/23/2017	P6030043	13561	UR	2	1
1973542828	4/23/2017	P6030044	13561	P	2	0

i coded like this;

proc print data=Julychub.MHDL171819_B;
var surrg_spec_id testtype1 result1 lab_patientid SPEC_COLLECT_DATE;
where surrg_spec_id="MILP6050069";
run;
/*IDENTIFYIG DUPLICATE RECORDS*/
data Julychub.MHDL171819_COUNT;
set Julychub.MHDL171819_B;
by unique_id;
t_f=first.unique_id;
t_l=last.unique_id;
if t_f=1;
run;
proc sort data=Julychub.MHDL171819_B;
by unique_id surrg_spec_id ;
run;
proc transpose data=Julychub.MHDL171819_B out=lab_transpose;
by unique_id surrg_spec_id;
id spec_source testtype1;
var result1;
run;

it is not working. I spent the whole day today please help.  How can i get 12681 unique patient visits with  all of tests done on that visit? sole think like this;

UNIQUE_ID	SPEC_COLLECT_DATE	SPEC_ID	SPEC_ID	LAB_PATIENTID	SPEC_SOURCE	SPEC_SOURCE	TESTTYPE1	TESTTYPE2	RESULT1	RESULT2
3513742828	4/3/2017	P6030025	P6030026	21501	UR	U	2	1	0	1
1973542828	4/3/2017	P6030043	P6030044	13561	UR	P	2	1	1	0

[ScottBass]

Too much crud to wade through.  I read your post three times and still don't want to touch it.  Perhaps I'm just lazy (isn't that a hallmark of a good programmer 😉 )

1) Re-post or edit your original post.  Post a source/target or "have"/"want" data steps, using datalines, with representative samples of your source data and desired target data.

2) Post your code using the "Insert SAS Code" (running man) icon, so it does not get reformatted by the forum software.

3) Test your code by cut-and-pasting it back into SAS from your post, ensuring it runs as expected and without error.

4) Succinctly state your problem.  I think you want unique subjects, but your post title is "merging", there's not a single merge in your code, you've got a proc transpose in there, and your post is just too much work for me to try to figure out.

Expend as much effort in asking your question as you expect us in answering it.

[ballardw]

"It isn't working" is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.