Access Beginner
Active Member
- Joined
- Nov 8, 2010
- Messages
- 311
- Office Version
- 2016
- Platform
- Windows
Hello,
I have a list of dataset on a sheet called "DATA_HOLDINGS". This has columns of "libname","dataset","variable","type,"lenght" and "varnum". This list at the moment is 40,733 but will grow.
Under the column "dataset", there are dataset names with a list of variables for that dataset. The issue I have is that there are datasets that are exactly the same in terms of variables, but the datasets are
monthly eg. Dataset190710,Dataset190810.Dataset190910,Dataset191010 etc Each of these datasets will have the same variables. There are other datasets wich are monthly and these will have different variables.
Can someone please be able to create code so that a new sheet is created where a unqiue list of datasets are created,but still have all the "variable","type,"lenght" and "varnum"
.
Current Output
[TABLE="width: 479"]
<tbody>[TR]
[TD="width: 246, bgcolor: transparent"]libname
[/TD]
[TD="width: 105, bgcolor: transparent"]dataset
[/TD]
[TD="width: 94, bgcolor: transparent"]variable
[/TD]
[TD="width: 64, bgcolor: transparent"]type
[/TD]
[TD="width: 64, bgcolor: transparent"]length
[/TD]
[TD="width: 64, bgcolor: transparent"]varnum
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable1
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable2
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable3
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable4
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable5
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable6
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable7
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable1
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable2
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable3
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable4
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable5
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable6
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable7
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[/TR]
</tbody>[/TABLE]
Expected Output
[TABLE="width: 479"]
<tbody>[TR]
[TD="width: 246, bgcolor: transparent"]libname
[/TD]
[TD="width: 105, bgcolor: transparent"]dataset
[/TD]
[TD="width: 94, bgcolor: transparent"]variable
[/TD]
[TD="width: 64, bgcolor: transparent"]type
[/TD]
[TD="width: 64, bgcolor: transparent"]length
[/TD]
[TD="width: 64, bgcolor: transparent"]varnum
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable1
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable2
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable3
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable4
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable5
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable6
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable7
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[/TR]
</tbody>[/TABLE]
Some datsets have 2 years worth of monthly data. There will also be different libnames
I have a list of dataset on a sheet called "DATA_HOLDINGS". This has columns of "libname","dataset","variable","type,"lenght" and "varnum". This list at the moment is 40,733 but will grow.
Under the column "dataset", there are dataset names with a list of variables for that dataset. The issue I have is that there are datasets that are exactly the same in terms of variables, but the datasets are
monthly eg. Dataset190710,Dataset190810.Dataset190910,Dataset191010 etc Each of these datasets will have the same variables. There are other datasets wich are monthly and these will have different variables.
Can someone please be able to create code so that a new sheet is created where a unqiue list of datasets are created,but still have all the "variable","type,"lenght" and "varnum"
.
Current Output
[TABLE="width: 479"]
<tbody>[TR]
[TD="width: 246, bgcolor: transparent"]libname
[/TD]
[TD="width: 105, bgcolor: transparent"]dataset
[/TD]
[TD="width: 94, bgcolor: transparent"]variable
[/TD]
[TD="width: 64, bgcolor: transparent"]type
[/TD]
[TD="width: 64, bgcolor: transparent"]length
[/TD]
[TD="width: 64, bgcolor: transparent"]varnum
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable1
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable2
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable3
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable4
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable5
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable6
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable7
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable1
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable2
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]9
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable3
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable4
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable5
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable6
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190810
[/TD]
[TD="bgcolor: transparent"]Variable7
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[/TR]
</tbody>[/TABLE]
Expected Output
[TABLE="width: 479"]
<tbody>[TR]
[TD="width: 246, bgcolor: transparent"]libname
[/TD]
[TD="width: 105, bgcolor: transparent"]dataset
[/TD]
[TD="width: 94, bgcolor: transparent"]variable
[/TD]
[TD="width: 64, bgcolor: transparent"]type
[/TD]
[TD="width: 64, bgcolor: transparent"]length
[/TD]
[TD="width: 64, bgcolor: transparent"]varnum
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable1
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable2
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[TD="bgcolor: transparent, align: right"]2
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable3
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable4
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable5
[/TD]
[TD="bgcolor: transparent"]num
[/TD]
[TD="bgcolor: transparent, align: right"]8
[/TD]
[TD="bgcolor: transparent, align: right"]5
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable6
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]6
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Lib1
[/TD]
[TD="bgcolor: transparent"]Dataset190710
[/TD]
[TD="bgcolor: transparent"]Variable7
[/TD]
[TD="bgcolor: transparent"]char
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent, align: right"]7
[/TD]
[/TR]
</tbody>[/TABLE]
Some datsets have 2 years worth of monthly data. There will also be different libnames
Last edited: