How to create a table output showing repeat visits with the purpose of each visit

[nibira]

I am trying to tabulate an output in which the first column will show the repeat visits of each patient, the second column will show the date of the visit and then the third column(s) will show the purpose of each visit.

For example:

Name       Visit Date       Reason 1     Reason 2    Reason 3

John         08/29/2018                                              Yes

John         11/04/2019       Yes                                  Yes

Teresa       02/12/2017                          Yes              

Teresa       06/21/2018                          Yes              Yes

Teresa       12/12/2018       Yes                                         

I'll greatly appreciate simple SAS codes that are easy to understand. Thanks for your help.

[PaigeMiller]

Hello @nibira and welcome to the SAS Communities.

Some of us will not download Microsoft Office documents as they are a security risk. Could you please provide the data as described here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[ballardw]

Do you have a SAS data set to work with?

I couldn't tell if the spread sheet was supposed to be what you currently have, in which case making a clean SAS data set may be "not simple code", or what you want. If it is what you want then we need to know what you currently have for data.

[nibira]

Yes, I have the dataset to work with. The dataset has more than 30 variables. I need to extract the variables (as shown in the example) and consolidate the data.

[PaigeMiller]

For those of us who will not or cannot open .xlsx files, can you provide the SAS data set as requested above?

[ballardw]

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.

It may take a bit of trial and error to follow the instructions but the link will generate data step code of your SAS data set.

[nibira]

Thanks for the suggestion. Please find the data sample as following:

Create table sample(var1 varchar(50), var2 float, reason_1____ varchar(20), reason_2___ varchar(23), _reason_3 varchar(24));
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('John', 08/29/2018, '', '', 'Paying for clinic visits');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('John                              ', 11/04/2019, 'Insurance Navigation', '', 'Paying for clinic visits');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('Teresa', 02/12/2017, '', 'Access to medical needs', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('Teresa', 06/21/2018, '', 'Access to medical needs', 'Paying for clinic visits');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('Teresa', 12/12/2018, 'Insurance Navigation', '', '');

[PaigeMiller]

Hi, @nibira, twice we have now asked for your data in a very specific format, that will allow us to work with it and help your further. Other formats of providing this data are generally less effective for us, so please go to the link provided by @ballardw or myself and follow the instructions there.

[nibira]

Last time I used the second approach on the link.

Please find the log output as following:

data WORK.SAMPLE;
infile datalines dsd truncover;
input VAR1:$50. VAR2:MMDDYY10. Reason_1____:$20. Reason_2___:$23. _Reason_3:$24.;
format VAR2 MMDDYY10.;
label VAR1="Name      " VAR2="Visit Date      " Reason_1____="Reason 1    " Reason_2___="Reason 2   " _Reason_3=" Reason 3";
datalines;
John 08/29/2018 Paying for clinic visits
John                               11/04/2019 Insurance Navigation Paying for clinic visits
Teresa 02/12/2017 Access to medical needs
Teresa 06/21/2018 Access to medical needs Paying for clinic visits
Teresa 12/12/2018 Insurance Navigation
;;;;

[ballardw]

---

@nibira wrote:

Thanks for the suggestion. Please find the data sample as following:

Create table sample(var1 varchar(50), var2 float, reason_1____ varchar(20), reason_2___ varchar(23), _reason_3 varchar(24));
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('John', 08/29/2018, '', '', 'Paying for clinic visits');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('John                              ', 11/04/2019, 'Insurance Navigation', '', 'Paying for clinic visits');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('Teresa', 02/12/2017, '', 'Access to medical needs', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('Teresa', 06/21/2018, '', 'Access to medical needs', 'Paying for clinic visits');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('', ., '', '', '');
Insert into sample(var1, var2, reason_1____, reason_2___, _reason_3) Values('Teresa', 12/12/2018, 'Insurance Navigation', '', '');

---

Which is NOT acceptable SAS code. Throws a bunch of

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,
              a datetime constant, a missing value, ), +, ',', -, MISSING, NULL, USER.