Import files in one table and create a new variable corresponding to the file's names

[riccardo88]

Hallo Community,

I have ca. 100 files with the following name's structure: "BBB101-07022019-0934" which contains informations about:

- Test subject's ID: 101 (The "BBB" at the beginning is the same for every file)

- Measurement's date: 07022019

- Measurement's time: 0934

The files look like this:

I would like to do the following:

1) Import all the file in one table 

2) create for every file an additional variable containing the file's Name (so that I can divide it later in trhree subvariables and obatin "ID", "day", "time").

3) eliminate the upper part of the file (the White part with te general Informations about the file)

I solved the first Point with the following code:

proc import datafile='G:\SHF\Projekte\Aktiv\ATLAs (2016-2019)\Rohdaten\Study\Innocor\*.txt'
            out=Innocor_full
            dbms=dlm
            replace;
	   datarow=2;
     delimiter='09'x;
run;

but I need help for point number 2 and 3.

I'm using SAS 9.4

Thank you very much for your help!

Riccardo

[ballardw]

It will help if you provide the example data in a text format, not an image. Use a plain text editor such as Notepad, copy the lines of data and paste the result here into a code box opened using the forum's {I} icon.

I assume that you want some of the information that appears above the line that starts with TIME. Is the number of lines in that header section always the same? Do they always have the same row beginning such as "Nummer"?

If  we had text we could show how to write a data step that reads the pieces you want at the same time as the rest of the data. It would help to list any of the header information that you may want later other than those three elements you list.

Did the proc import bring in the variables you needed in the correct format, which assume would be numeric?

[riccardo88]

Hi Ballardw,

many thanks for your answer.

Here a sample of the data:

Innocor, von Innovision Dänemark
"Pro Atemzug"-Daten
--------------------------------------------------------------------
Nummer	101
Nachname	ATL
Vorname(n)	-
Größe	170.00
Gewicht	75.00
Geburtsdatum	01-01-1993

--------------------------------------------------------------------
Test durchgeführt:	07-02-2019	9:34:20

Time	Arbeitsbelastung	HR	Atemfrequenz	Ve	VCO2	VO2	R	VO2/HR	FO2et	FCO2et	Ve/VCO2	Ve/VO2	SpO2	VO2/kg	VO2/BSA	FiCO2	FiO2	Va	Vt	Vd	Geschwindigkeit	Steigung	DIA	SYS	MAP	VeVCO2slp	VO2loadSlp	CoVo2slp	BR%	FEV1	FVC	PEF	MEF75	MEF50	MEF25	FET	FEV1%	MVV	Größe	Gewicht	BSA	Alter	Umg. Druck	Umg. Temp	Umg. Feucht	cvp1	PO2	PCO2	pH	BE	HCO3	
Sec.																														l	l	l/s	l/s	l/s	l/s	s		l/min			m2					mmHg	mmHg	mmHg		mmo/l	mmol/l	
5.71	0	86.6666666666667	19.6078431372549	13.6573671806811	0.262892022137917	0.33883954047642	0.775859929948794	3.90968700549715	15.22296875	4.29359375	44.4173806753726	34.4616658593035	98	4.5178605396856	181.824020006407	0.1275244140625	20.3928125	9.06877690623077	0.696525726214734	0.133018103996965	0																		170	75	1.86355763371902	26	1000	23	30							
10.09	0	85	13.6986301369863	9.79250804627849	0.200375356694935	0.252087805219194	0.794863347398759	2.9657388849317	15.42453125	4.266875	41.9659709713966	33.3572121631634	98	3.36117073625593	135.272341814357	0.152509765625	20.348125	6.58678059426528	0.71485308737833	0.133018103996965	0																		170	75	1.86355763371902	26	1000	23	30							
13.71	0	83	16.5745856353591	9.05946308217038	0.15993651903984	0.20800220455958	0.768917422671009	2.50605065734433	15.4421875	4.181171875	46.1772581855393	35.5064983500386	98	2.77336272746106	111.615654271169	0.13502685546875	20.449375	5.91460182939175	0.546587605957613	0.0887399622509773	0																		170	75	1.86355763371902	26	1000	23	30							
18.43	0	83	12.7118644067797	10.3474613736413	0.141691862890056	0.186458387481383	0.759911446215864	2.24648659616124	14.75	4.645859375	63.9667154040408	48.6090392123632	98	2.48611183308511	100.055068921735	0.15125	20.460625	7.9355127009594	0.81400029472645	0.0887399622509773	0																		170	75	1.86355763371902	26	1000	23	30							
22.98	0	83	13.1868131868132	9.74451086266263	0.306908637798289	0.409195717581188	0.750028958300121	4.93006888652034	14.73953125	4.649375	27.4109024468825	20.5589706083015	98	5.4559429010825	219.577709955003	0.14106201171875	20.3809375	7.242445426386	0.738958740418582	0.0887399622509773	0																		170	75	1.86355763371902	26	1000	23	30							
28.71	0	86	10.4712041884817	16.6853841328195	0.421930140115425	0.516634876987974	0.816689230458684	6.00738229055783	15.711875	4.16859375	37.0388152538892	30.2492015268001	98	6.88846502650632	277.230426169835	0.12505126953125	20.400625	13.5742336585385	1.59345418468426	0.196114870293837	0																		170	75	1.86355763371902	26	1000	23	30							
32.55	0	86	15.625	8.24244055898938	0.145907386894171	0.174955706742466	0.833967577341997	2.03436868305193	15.7828125	4.09828125	45.674970272911	38.0914443036673	98	2.33274275656621	93.8826380128175	0.13190673828125	20.383125	5.9099417191454	0.52751619577532	0.0482799257500143	0																		170	75	1.86355763371902	26	1000	23	30							
37.19	0	83	12.9310344827586	6.33536199328416	0.115180893738459	0.141365534203144	0.814773518790961	1.70319920726679	15.630625	4.193515625	43.6645987653613	35.5767587826489	98	1.88487378937525	75.8578815300854	0.1333447265625	20.4075	3.95109025976385	0.489934660813975	0.0833836807255711	0																		170	75	1.86355763371902	26	1000	23	30							
41.02	0	84.25	15.6657963446475	9.27962304197726	0.174840339772174	0.220832091373705	0.791734293166201	2.62115241986593	15.365625	4.310703125	44.0251810377281	34.8562455904197	98	2.94442788498273	118.500274624187	0.1366943359375	20.405625	5.92799698118713	0.592349270846215	0.112945463547104	0																		170	75	1.86355763371902	26	1000	23	30							
44.8	0	83.25	15.8730158730159	7.73802012702268	0.136018541716549	0.171822049451662	0.791624486790997	2.06392852194189	15.31515625	4.30796875	45.1030090929261	35.7046464259173	98	2.2909606593555	92.2010923315338	0.1810693359375	20.32	4.47852898733533	0.487495268002429	0.104347941800303	0																		170	75	1.86355763371902	26	1000	23	30							
48.59	0	82	15.8311345646438	8.59846051206866	0.1731244683582	0.22496604312773	0.769558222882129	2.74348833082598	15.12390625	4.42109375	40.4305410287667	31.1136553042607	98	2.99954724170307	120.718586351834	0.13092529296875	20.381875	5.77267654679437	0.543136089012337	0.0774953538064927	0																		170	75	1.86355763371902	26	1000	23	30							
52.41	0	82.75	15.7068062827225	7.49195576981453	0.130974990149388	0.170449970055112	0.768407234727231	2.05981836924607	15.1946875	4.334609375	45.0892825303805	34.6469309050045	98	2.27266626740149	91.4648235026422	0.1464404296875	20.3984375	4.83202530522098	0.476987850678192	0.0683489062457892	0																		170	75	1.86355763371902	26	1000	23	30							
56.24	0	83.3333333333333	15.6657963446475	7.6925697145026	0.150843269421084	0.198932105060424	0.758265084337525	2.38718526072509	15.12359375	4.363828125	40.5077688062835	30.7156267302215	98.3333333333333	2.65242806747232	106.748566001377	0.13260009765625	20.34375	5.01690741609424	0.491042366775749	0.0697964433817334	0																		170	75	1.86355763371902	26	1000	23	30							
60.07	0	84.5	15.6657963446475	7.93784638328618	0.151982715461823	0.20354391321647	0.746682684144864	2.40880370670379	14.87125	4.457578125	41.8179194467232	31.224716337833	98.25	2.71391884288627	109.22329931394	0.1471533203125	20.381875	5.35037533215779	0.506699194133101	0.0641669020970285	0																		170	75	1.86355763371902	26	1000	23	30							
65.49	0	84.6666666666667	20.4081632653061	17.0255707873831	0.200783612664238	0.275440619151741	0.728954259842213	3.25323565927253	14.531875	4.6703125	74.5297193282971	54.3287563892067	98	3.67254158868988	147.803649411183	0.13590087890625	20.39375	12.486941607484	0.834252968581772	0.121392829815057	0																		170	75	1.86355763371902	26	1000	23	30							
68.89	0	86	17.6470588235294	11.3930782575312	0.333199814561887	0.459969117329989	0.724396056187494	5.34847810848825	14.469375	4.69203125	28.8437294870394	20.8942838861503	98	6.13292156439986	246.823124226133	0.17279052734375	20.345625	6.83933769838043	0.64560776792677	0.157045298351879	0																		170	75	1.86355763371902	26	1000	23	30							
72.82	0	86.25	15.2671755725191	7.83379384352958	0.180980439336628	0.238380738694602	0.759207477616255	2.76383465153161	15.87734375	3.7822265625	34.7651333689285	26.3939492140169	98	3.17840984926135	127.917019780534	0.1843408203125	20.3628125	5.17344820401572	0.513113496751188	0.0732526393881581	0																		170	75	1.86355763371902	26	1000	23	30							
76.22	0	89.3333333333333	17.6470588235294	9.8939304919259	0.143981738900737	0.190769668141954	0.754741255793339	2.13548135979799	15.24046875	4.238046875	56.3375440016159	42.5202687080921	98	2.54359557522605	102.368536765478	0.1403125	20.36875	6.46309296003413	0.560656061209134	0.0934141268072006	0																		170	75	1.86355763371902	26	1000	23	30							

Those information on the top are needed but I thought it would be out of my SAS skills to include them.

If it's possible to include them I would need:

- Nummer (that's the same included in the filename)

- Größe

- Gewicht

- Geburtsdatum

- The Date and time after "Test durchgeführt:" (Those are also include in the filename)

The number of lines and the text in the heading section are always the same for all files.

The number of lines of data differs beetwen files.

My code worked correclty and the data are numeric.

Thank you,

Riccardo

[Ksharp]

This code could give you a start .

data want;
infile cards expandtabs truncover;
length Nummer Nachname Test_durchg $ 40;
retain Nummer Nachname Test_durchg;
input;
if _infile_ =: 'Nummer' then  Nummer=scan(_infile_,-1,' ');
if _infile_ =: 'Nachname' then  Nachname=scan(_infile_,-1,' ');
if _infile_ =: 'Test durchgeführt:' then  Test_durchg=substr(_infile_,anydigit(_infile_));

if _n_>14 then do;
input Time	Arbeitsbelastung	HR	Atemfrequenz	Ve	VCO2	VO2	R	VO2HR	FO2et	FCO2et	VeVCO2	VeVO2	SpO2	VO2kg	VO2BSA	FiCO2	FiO2	Va	Vt	Vd	Geschwindigkeit	Steigung	DIA	SYS	MAP	VeVCO2slp	VO2loadSlp	CoVo2slp	BRFEV1	FVC	PEF	MEF75	MEF50	MEF25	FETFEV1	MVV	Gr	Gewicht	BSA	Alter Druck	 Temp Feucht	cvp1	PO2	PCO2	pH	BE	HCO3;	
output;
end;
cards4;
Innocor, von Innovision Dänemark
"Pro Atemzug"-Daten
--------------------------------------------------------------------
Nummer	101
Nachname	ATL
Vorname(n)	-
Größe	170.00
Gewicht	75.00
Geburtsdatum	01-01-1993

--------------------------------------------------------------------
Test durchgeführt:	07-02-2019	9:34:20

Time	Arbeitsbelastung	HR	Atemfrequenz	Ve	VCO2	VO2	R	VO2/HR	FO2et	FCO2et	Ve/VCO2	Ve/VO2	SpO2	VO2/kg	VO2/BSA	FiCO2	FiO2	Va	Vt	Vd	Geschwindigkeit	Steigung	DIA	SYS	MAP	VeVCO2slp	VO2loadSlp	CoVo2slp	BR%	FEV1	FVC	PEF	MEF75	MEF50	MEF25	FET	FEV1%	MVV	Größe	Gewicht	BSA	Alter	Umg. Druck	Umg. Temp	Umg. Feucht	cvp1	PO2	PCO2	pH	BE	HCO3	
Sec.																														l	l	l/s	l/s	l/s	l/s	s		l/min			m2					mmHg	mmHg	mmHg		mmo/l	mmol/l	
5.71	0	86.6666666666667	19.6078431372549	13.6573671806811	0.262892022137917	0.33883954047642	0.775859929948794	3.90968700549715	15.22296875	4.29359375	44.4173806753726	34.4616658593035	98	4.5178605396856	181.824020006407	0.1275244140625	20.3928125	9.06877690623077	0.696525726214734	0.133018103996965	0																		170	75	1.86355763371902	26	1000	23	30							
10.09	0	85	13.6986301369863	9.79250804627849	0.200375356694935	0.252087805219194	0.794863347398759	2.9657388849317	15.42453125	4.266875	41.9659709713966	33.3572121631634	98	3.36117073625593	135.272341814357	0.152509765625	20.348125	6.58678059426528	0.71485308737833	0.133018103996965	0																		170	75	1.86355763371902	26	1000	23	30							
13.71	0	83	16.5745856353591	9.05946308217038	0.15993651903984	0.20800220455958	0.768917422671009	2.50605065734433	15.4421875	4.181171875	46.1772581855393	35.5064983500386	98	2.77336272746106	111.615654271169	0.13502685546875	20.449375	5.91460182939175	0.546587605957613	0.0887399622509773	0																		170	75	1.86355763371902	26	1000	23	30							
18.43	0	83	12.7118644067797	10.3474613736413	0.141691862890056	0.186458387481383	0.759911446215864	2.24648659616124	14.75	4.645859375	63.9667154040408	48.6090392123632	98	2.48611183308511	100.055068921735	0.15125	20.460625	7.9355127009594	0.81400029472645	0.0887399622509773	0																		170	75	1.86355763371902	26	1000	23	30							
22.98	0	83	13.1868131868132	9.74451086266263	0.306908637798289	0.409195717581188	0.750028958300121	4.93006888652034	14.73953125	4.649375	27.4109024468825	20.5589706083015	98	5.4559429010825	219.577709955003	0.14106201171875	20.3809375	7.242445426386	0.738958740418582	0.0887399622509773	0																		170	75	1.86355763371902	26	1000	23	30							
28.71	0	86	10.4712041884817	16.6853841328195	0.421930140115425	0.516634876987974	0.816689230458684	6.00738229055783	15.711875	4.16859375	37.0388152538892	30.2492015268001	98	6.88846502650632	277.230426169835	0.12505126953125	20.400625	13.5742336585385	1.59345418468426	0.196114870293837	0																		170	75	1.86355763371902	26	1000	23	30							
32.55	0	86	15.625	8.24244055898938	0.145907386894171	0.174955706742466	0.833967577341997	2.03436868305193	15.7828125	4.09828125	45.674970272911	38.0914443036673	98	2.33274275656621	93.8826380128175	0.13190673828125	20.383125	5.9099417191454	0.52751619577532	0.0482799257500143	0																		170	75	1.86355763371902	26	1000	23	30							
37.19	0	83	12.9310344827586	6.33536199328416	0.115180893738459	0.141365534203144	0.814773518790961	1.70319920726679	15.630625	4.193515625	43.6645987653613	35.5767587826489	98	1.88487378937525	75.8578815300854	0.1333447265625	20.4075	3.95109025976385	0.489934660813975	0.0833836807255711	0																		170	75	1.86355763371902	26	1000	23	30							
41.02	0	84.25	15.6657963446475	9.27962304197726	0.174840339772174	0.220832091373705	0.791734293166201	2.62115241986593	15.365625	4.310703125	44.0251810377281	34.8562455904197	98	2.94442788498273	118.500274624187	0.1366943359375	20.405625	5.92799698118713	0.592349270846215	0.112945463547104	0																		170	75	1.86355763371902	26	1000	23	30							
44.8	0	83.25	15.8730158730159	7.73802012702268	0.136018541716549	0.171822049451662	0.791624486790997	2.06392852194189	15.31515625	4.30796875	45.1030090929261	35.7046464259173	98	2.2909606593555	92.2010923315338	0.1810693359375	20.32	4.47852898733533	0.487495268002429	0.104347941800303	0																		170	75	1.86355763371902	26	1000	23	30							
48.59	0	82	15.8311345646438	8.59846051206866	0.1731244683582	0.22496604312773	0.769558222882129	2.74348833082598	15.12390625	4.42109375	40.4305410287667	31.1136553042607	98	2.99954724170307	120.718586351834	0.13092529296875	20.381875	5.77267654679437	0.543136089012337	0.0774953538064927	0																		170	75	1.86355763371902	26	1000	23	30							
52.41	0	82.75	15.7068062827225	7.49195576981453	0.130974990149388	0.170449970055112	0.768407234727231	2.05981836924607	15.1946875	4.334609375	45.0892825303805	34.6469309050045	98	2.27266626740149	91.4648235026422	0.1464404296875	20.3984375	4.83202530522098	0.476987850678192	0.0683489062457892	0																		170	75	1.86355763371902	26	1000	23	30							
56.24	0	83.3333333333333	15.6657963446475	7.6925697145026	0.150843269421084	0.198932105060424	0.758265084337525	2.38718526072509	15.12359375	4.363828125	40.5077688062835	30.7156267302215	98.3333333333333	2.65242806747232	106.748566001377	0.13260009765625	20.34375	5.01690741609424	0.491042366775749	0.0697964433817334	0																		170	75	1.86355763371902	26	1000	23	30							
60.07	0	84.5	15.6657963446475	7.93784638328618	0.151982715461823	0.20354391321647	0.746682684144864	2.40880370670379	14.87125	4.457578125	41.8179194467232	31.224716337833	98.25	2.71391884288627	109.22329931394	0.1471533203125	20.381875	5.35037533215779	0.506699194133101	0.0641669020970285	0																		170	75	1.86355763371902	26	1000	23	30							
65.49	0	84.6666666666667	20.4081632653061	17.0255707873831	0.200783612664238	0.275440619151741	0.728954259842213	3.25323565927253	14.531875	4.6703125	74.5297193282971	54.3287563892067	98	3.67254158868988	147.803649411183	0.13590087890625	20.39375	12.486941607484	0.834252968581772	0.121392829815057	0																		170	75	1.86355763371902	26	1000	23	30							
68.89	0	86	17.6470588235294	11.3930782575312	0.333199814561887	0.459969117329989	0.724396056187494	5.34847810848825	14.469375	4.69203125	28.8437294870394	20.8942838861503	98	6.13292156439986	246.823124226133	0.17279052734375	20.345625	6.83933769838043	0.64560776792677	0.157045298351879	0																		170	75	1.86355763371902	26	1000	23	30							
72.82	0	86.25	15.2671755725191	7.83379384352958	0.180980439336628	0.238380738694602	0.759207477616255	2.76383465153161	15.87734375	3.7822265625	34.7651333689285	26.3939492140169	98	3.17840984926135	127.917019780534	0.1843408203125	20.3628125	5.17344820401572	0.513113496751188	0.0732526393881581	0																		170	75	1.86355763371902	26	1000	23	30							
76.22	0	89.3333333333333	17.6470588235294	9.8939304919259	0.143981738900737	0.190769668141954	0.754741255793339	2.13548135979799	15.24046875	4.238046875	56.3375440016159	42.5202687080921	98	2.54359557522605	102.368536765478	0.1403125	20.36875	6.46309296003413	0.560656061209134	0.0934141268072006	0																		170	75	1.86355763371902	26	1000	23	30							
;;;;
run;

[riccardo88]

Thank you so much! that works! Just the position of the missing values is wrong and they are all shifted to the end.

I've tried to add "DSD" after infile but didn't work.

Could you please help me with that?

[Ksharp]

You need to know which value belong to which variable , I have no time to check it for you .

Good Luck.