Move to the next iteration if variable exists

[Lisa NingLi]

I'm using Stata 16 on Windows. My data looks like (A):

id VisitDate MedClassId
33 2010 Corticost
33 2010 Antidepres
33 2012 Antidepres
33 2012 Vit D
33 2012 Corticost
33 2013 Vit D
33 2013 Corticost
33 2013 Antidepres
33 2015 Corticost
33 2015 Vit D
33 2015 Antidepres

where MedClassId is a numeric variable (displayed are labels).

The aim is to convert (A) to (B):

id VisitDate Antidepres Corticost Vit_D
33 2010 1 1 0
33 2012 1 1 1
33 2013 1 1 1
33 2015 1 1 1


After reshaping to wide, I had

id VisitDate Antidepres1 Corticost1 Vit_D1 Antidepres2 Corticost2 Vit_D2 Antidepres3 Corticost3
33 2010 0 1 0 1 0 0 . .
33 2012 1 0 0 0 0 1 0 1
33 2013 0 0 1 0 1 0 1 0
33 2015 0 1 0 0 0 1 1 0

As Antidepres1 Antidepres2 Antidepres3 are on different dates, I manually used
-egen Antidepres=rowmax(Antidepres*)- to get the information of Antidepres into a single variable, Antidepres.
Similarly for Corticost and Vit_D. That gave me form B.

There were thousands of ids and different ids had different drugs, how can we softcode (A) to (B)? Thank you for the help.

*---------------------------begin failed----------------------*
* I 1st added a prefix M_ to each variable of interest in the wide data. Then

preserve
keep M_*
ds
local varlist=r(varlist)
restore

foreach q in `varlist' {
di
local x=substr("`q'",1,strlen("`q'")-1)
cap confirm newvariable `x'
if _rc==0 {
gen `x'=rowmax(`x'*)
}
if _rc!=0 {
/* do nothing but move to the next `q' */
}
save test, replace
}
*----------------------end--------------------------*

[Clyde Schechter]

A different approach will be more effective and efficient:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id int visitdate byte medclassid
33 2010 2
33 2010 1
33 2012 1
33 2012 3
33 2012 2
33 2013 3
33 2013 2
33 2013 1
33 2015 2
33 2015 3
33 2015 1
end
label values medclassid medclassid
label def medclassid 1 "Antidepres", modify
label def medclassid 2 "Corticost", modify
label def medclassid 3 "Vit D", modify

//    STORE THE VALUE LABELS IN LOCAL MACROS FOR LATER USE
levelsof medclassid, local(medclasses)
local varnames
foreach m of local medclasses {
    local medclass`m' = strtoname(`"`:label (medclassid) `m''"')
    gen byte `medclass`m'' = `m'.medclassid
    local varnames `varnames' `medclass`m''
}

collapse (max) `varnames', by(id visitdate)

That said, do you have a good reason for going to the wide layout? There are only a few things in Stata that are best done (or even doable at all) with wide data. Unless you know for a fact that you will be doing one of those, you are probably better off just staying with the data as they were.

In the future, when showing data examples, please use the -dataex- command to do so, as I have here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Lisa NingLi]

Hi Clyde
You are so quick! Your solution arrived when I got back from lunch! The codes worked very well! Of course, -Collapse- is the one to use! Thank you so much for the help and explanation. I'll use -dataex- next time to save time inputting data. Many thanks once more!

[Lisa NingLi]

Hi Clyde
I'm trying to digest strtoname(`"`:label (MedClassId) `m''"'). It's really powerful! Delighted to have learnt something wonderful. Thank you so much. Cheers.